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

[canadian86]

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]

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>