Results 1 to 2 of 2

Thread: Calculate Cumulative interest paid at Month # 15 for Mortgage amortized for 25 years
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2011
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,487
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

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

    Use the following to approximate cumulative interest between any period of months:

    =-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.
    Last edited by joeu2004; Jan 22nd, 2019 at 03:24 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •