# Is there a function/formula for the principal and interest lines in a mortgage graph?

When you use a mortgage calculator sometimes the results coming with a graph as well showing interest and principal paid every month. Image below... Is there a formula/function that defines those lines individually, such that I can plug in any month number and get the amount paid for the respective line. For example, for the interest paid line, given month 14, what is the interest paid for month 14.

On the Wikipedia page for Mortgage Calculator, they have this graph (below)... The blue shaded line/area is remaining principal balance. I was able to find the forumla/function of that line on the page. So for any given month, you can calculate the remaining principal. On the same page, there is a formula to calculate the total/aggregate interest paid for any given month.

But I'm not able to find a formula/function to find the principal paid or interest paid for any given month without going through multiple steps.

• Mortgage interest due is no more than the current balance multiplied by 1/12th of the mortgage’s interest rate. Plug that into a spreadsheet with 360 (or 180 or 240) rows and graph away at anything you’re looking for. Dec 7, 2022 at 20:37

The principal remaining and accumulated principal repaid in month `n` are

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

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

E.g. where

``````principal        s = 250000
monthly rate     r = 0.04/12
no. months       n = 12*30 = 360

repayment amt.   d = r s/(1 - (1 + r)^-n) = 1193.54

n = 360
accpr(n) = (d - r s) ((1 + r)^n - 1)/r = 250000
``````

or accumulated principal repaid up to month 14

``````n = 14
accpr(n) = (d - r s) ((1 + r)^n - 1)/r = 5153.60
``````

Equivalent in Excel, CUMPRINC

``````=CUMPRINC(0.04/12,360,250000,1,14,0)
``````

Interest paid in period `n`

``````intn(n) = d + (r s - d) (1 + r)^(n - 1)
``````

And accumulated interest is

``````n = 360
accint(n) = (d + d n r - d (1 + r)^n - r s + r (1 + r)^n s)/r = 179673.77
``````

Check : Accumulated interest to month 360 equals total payments less principal

``````d n - s = 179673.77
``````

Accumulated interest paid up to month 14

``````n = 14
accint(n) = (d + d n r - d (1 + r)^n - r s + r (1 + r)^n s)/r = 11555.93
``````

Equivalent in Excel, CUMIPMT

``````=CUMIPMT(0.04/12,360,250000,1,14,0)
``````

Plotting `p(n)`, `accpr(n)`, `accint(n)` and `d n` for n = 1 to 360 • This is fantastic. Thank you so much. Dec 8, 2022 at 18:10