1

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)

1
  • Please don't post images of text. Post text as text so that it's searchable. Commented Jun 8, 2022 at 16:25

2 Answers 2

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.

4
  • Ok thank you! It gives me a result closer to the bank's result when I do this: =-CUMIPMT(3.879%/12,360,556050,DATEDIF("2018-07-20","2021-01-31", "M"),DATEDIF("2018-07-20","2021-12-31","M"),0) However, it's still not equal. I get 20 411.56$ but the bank statement says 20 408.88$ Commented Jun 8, 2022 at 15:37
  • It's a lot closer if you use the 3.897% rate. Maybe that rate includes a discount of some sort?
    – D Stanley
    Commented Jun 8, 2022 at 15:48
  • Thanks for your reply, I'm not sure. The difference is not very so maybe it's fine? I was wondering if there is a different formula or method to find the interest between two dates without having to think about if there is an extra month or not? I'd basically like to remove the chance of error and just put in the start and end dates. Ex: January 1 to December 31 or February 17 to November 23. Commented Jun 8, 2022 at 16:04
  • Not that I can think of - you could calculate the period numbers separately and have a check to show that you're calculating interest for N periods (end - start + 1). That would have shown you that you're using 13 periods instead of 12.
    – D Stanley
    Commented Jun 8, 2022 at 16:10
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .