Amortization Calculation in Excel

Submitted by Fun_Adagio5494 t3_zzt3rw in personalfinance

Could someone confirm I'm using the excel formulas correctly on the following?

I'm trying to calculate the savings in final interest payment for a mortgage by making extra payments.

Let's say, someone got a mortgage loan on $100k for 30 years at 3.5%. Using the excel formula =CUMIPMT(3.5%/12,30*12,100000,1,30*12,0) the person would pay $61,656.09 in interest at the end of this Loan in addition to the principal.

If someone has been making extra payments on the loan and after payment number 20, they have left $75k on the principal, would the formula be as follow? =CUMIPMT(3.5%/12,30*12,75000,20,30*12,0)

If this formula is the correct one, would mean the person would pay $42,145.67 in interest on the remaining of the principal. To understand how much one saved on interest by making extra payments, one would add the new amount in interest plus the actual interest paid during the 20 payments and subtract this from the original interest amount.

Is this math sound?

Edit: Minor fixes and grammatical errors.

2

Comments

You must log in or register to comment.

chamon- t1_j2dhbd2 wrote

Maybe you can check the correct answer with an online Amortization calc or with chatgpt

Edit: sorry, i just stumbled on this post but dont know much excel formula

4

AdditionalAttorney t1_j2djtyj wrote

I would run an amortization calculator on your base case. And then look at the actually amortization table (where there’s a line for every year)

I would then Recreate that table in excel w formulas

And then add a column for “extra payment”.

Should just be basic addition, subtraction, and multiplication

2

Citryphus t1_j2dkrcx wrote

Changing the present value of the loan from $100k to $75k calculates a completely different payment, so you can't CUMIPMT in that way. Paying extra on a loan reduces the number of payments, so you could use NPER to calculate the number of periods with a given payment, then calculate CUMIPMT of the original loan between that period and 360 months. That amount is your savings.

However, I question the reasoning behind your goal. Calculating cumulative interest over 30 years ignores the time value of money and gives a misleadingly large amount. The present value of the interest payments should be how you compare the two scenarios.

1

MattyHurricane t1_j2e1jch wrote

I understand where you're going, but you're trying to figure out how to make a pencil while sitting at a desk with a hundred pencils on it. Use a readily available online amortization calculator. Start to finish, you can have the answer in 30 seconds.

1