Calculating cumulated interest of mortgage within a specific time span

Question

I like to use google sheets because of its ease of access with just a link. I have a mortgage and I need to be able to calculate the interest within a year (but I'd like to be able to do it within any two arbitrary dates).

Mortgage informmation

![][1]

What I have tried in google sheets: =-CUMIPMT(3.879%/12,360,556050,DATEDIF("2018-07-20","2021-01-01", "M"),DATEDIF("2018-07-20","2021-12-31","M"),0)

This doesn't give me the same interest as the one the bank gave me (20 408.88$), instead, it gives me 22 131.58$. I believe there's an extra month of interest. However, the main difficulty is that the year is off period (payments on the 20th of each month but the fiscal year is from January 1st to December 31). Ideally, I'd like to be able to pick two dates and then just refer to the specific case of those two dates and have the formula calculate the interest. ex: A1 (start date) and B1 (end date)

Answer 1

You're just including an extra month. Change your start date to 2021-01-31 so that your starting and ending periods will be 30 and 41 instead of 29 and 41.

or, more genericall, change your formula to

=-CUMIPMT(3.879%/12,360,556050,DATEDIF("2018-07-20","2021-01-01", "M")+1,DATEDIF("2018-07-20","2021-12-31","M"),0)

which adds one to the starting period if you're calculating the number of months between two dates. DATEDIF will give you the number of WHOLE months between two dates, so your "starting period" is equivalent to the Dec 2020 payment, not the Jan 31, so you're including the Dec 2020 interest in your cumulative total.

Answer 2

The calculations below match the OP's second result: $20,411.56

With first and last payments on 2018-8-20 and 2048-7-20 respectively

principal     s = 556050
no. months    n = 30*12
monthly rate  r = 0.03879/12
payment amt.  d = r (1 + 1/((1 + r)^n - 1)) s = 2616.03

Interest paid on a specific month is given by int(x) (from here)

int(x) = d + (1 + r)^(x - 1) (r s - d)

e.g. int(30) = d + (1 + r)^(30 - 1) (r s - d) = 1717.12

Month              Target   Calculated   Diff
 30   2021-1-20    1716.89    1717.12    0.23
 31   2021-2-20    1713.99    1714.21    0.22
 32   2021-3-20    1711.07    1711.30    0.23
 33   2021-4-20    1708.15    1708.37    0.22
 34   2021-5-20    1705.22    1705.44    0.22
 35   2021-6-20    1702.27    1702.50    0.23
 36   2021-7-20    1699.32    1699.54    0.22
 37   2021-8-20    1696.35    1696.58    0.23
 38   2021-9-20    1693.39    1693.61    0.22
 39   2021-10-20   1690.41    1690.63    0.22
 40   2021-11-20   1687.41    1687.64    0.23
 41   2021-12-20   1684.41    1684.63    0.22
                  20408.88   20411.56

The cumulative interest can also be calculated directly

interestsofar(x) = (d - d (1 + r)^x - r s + r (1 + r)^x s + d r x)/r

interestsofar(41) - interestsofar(29) = 20411.56

Try a shortened first period

The bank's figures can be better matched by shortening the first period. Using the formula here and shortening by 1.23 days (puzzling, I know)

s = 556050
n = 30*12
r = 0.03879/12
a = -1.23/(365/12)
d = (r (1 + r)^(a + n) s)/((1 + r)^n - 1) = 2615.68

The recurrence formulae int and interestsofar operate on a standard loan, so resetting the principal and number of months to after the first shortened period, from which point the loan is standard.

s = s (1 + r)^(1 + a) - d =  555158.95
n = n - 1 = 359

As a check, the standard payment formula finds the payment unchanged

d = r (1 + 1/((1 + r)^n - 1)) s = 2615.68

In this reset loan 2021-1-20 is month 29

int(29) = d + (1 + r)^(29 - 1) (r s - d) = 1716.89

and the interest from January to December 2021 is

interestsofar[40] - interestsofar[28] = 20408.90