Calculate Cumulative interest paid at Month # 15 for Mortgage amortized for 25 years

canadian86

Board Regular
Joined
Feb 6, 2011
Messages
51
Dear all,

Is there a way to:

Calculate Cumulative interest paid at Month # 15 ("n") for Mortgage amortized for 25 years

using ONE line.

I want a single formula whereby I can just change "n" and it gives me the cumulative interest up to that point. All other mortgage elements (interest rate, # of payments per year, amortization duration) etc stay the same.

I am actually trying to do this on Google sheets instead of Excel, but if you know how to do it on either, that's fine.

Thanks!
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,601
Office Version
2010
Platform
Windows
Use the following to approximate cumulative interest between any period of months:

<bdo dir="ltr">=-CUMIPMT(B3/12,B2,B1,B4,B5,0)

where B3 is the annual rate, B2 is the total term of the loan in months (300 = 25*12), B1 is the total loan (e.g. 100000), B4 is the starting month# (e.g. 1), and B5 is the ending month# (e.g. 15). Of course, you can enter the values directly into the formula instead of using cell references.

I confirmed that that works with Google sheets.

Note: B3/12 is presumed to derive the monthly rate. For EU loans, we might use (1+B3)^(1/12)-1. For Canadian loans, we might use (1+B3/2)^(1/6)-1. You might double-check with your lender; or ask the lender to tell you the monthly rate.

I say the formula approximates the cumulative interest because: (a) CUMIPMT calculates and uses an "exact" payment internally, whereas the actual payment is rounded to the cent, and the lender might choose to round (up?) to less precision, e.g. to the dollar; and (b) the lender might choose to round actual interest paid per month to the cent, or to some greater precision (more decimal places) that is different from the "exact" precision that CUMIPMT calculates and uses internally.
</bdo>
 
Last edited:

Forum statistics

Threads
1,085,321
Messages
5,382,950
Members
401,811
Latest member
ngoctinh87

Some videos you may like

This Week's Hot Topics

Top