# How do I calculate the principal paid down on a mortgage?

I'm looking for an equation to find out the current principal on a mortgage. I have the following variables:

• n periods
• monthly payment amount
• interest rate of mortgage

I'd like to know how much I'd have in principal paid off against the mortgage after n periods.

• You also need the original amount... – DJohnM Oct 4 '16 at 1:15
• Can you clarify whether you mean in month n you want the amount of principal repaid, or you want the amount of principal remaining, i.e. the balance? – Chris Degnen Dec 17 '16 at 12:43

These are some great answers & I don't want to take away from the detail they provide, but I saw in a comment you mentioned you were looking for a Google Sheets solution for this.

An easy Google Sheets solution for cumulative principal paid is to use the =CUMPRINC function.

Example: \$500,000 loan, 5% interest, 20 year term, determine the cumulative principal paid after 5 years.

Formula: `=CUMPRINC(0.05/12,20*12,500000,1,5*12,0)`

Result: \$-82,725.68

Assuming that the original amount is C . Then, by the time of the Nth payment this original debt will have grown by compound interest. the new value will be :

New Principal = C x (1+i)^n

Of course, you've also been making payments; they constitute an ordinary annuity, and the future value is given by: So, just subtract the future value of the payments from the future value of the original debt to get the balance owing after the Nth payment...

• That seems to work out when I run it against my Excel Spreadsheet calculated values. First I'd say that after you do the subtraction, you'd have to convert it back into Present Value. Secondly it feels like a long winded process. There are 3 calculations, Future Value of the Property, then Future Value of the Annuity, then bringing the result into Present Value. Thanks though! It is something I can put in Google Sheets – Sebastian Patten Dec 16 '16 at 21:48

The question is: "I'd like to know how much I'd have in principal paid off against the mortgage after n periods."

It is slightly unclear whether you want the principal repaid or the principal remaining so here are formulas for the principal remaining in month n, the principal repaid in month n, and the accumulated principal repaid in month n.

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

pr[n] = (d - r s) (r + 1)^(n - 1)

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

where

``````p[n] is the principal remaining in month n, i.e the balance
pr[n] is the principal repayment in month n
accpr[n] is the accumulated principal repaid in month n

s is the initial loan principal
r is the monthly interest rate i.e. nominal annual rate ÷ 12
d is the regular monthly payment
``````

Example

Taking a £1000 loan over 3 years with 10% interest per month (rather high, but it's just an example), the monthly repayment `d` by standard formula is

``````s = 1000
r = 0.1
n = 36

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

Using these figures in a calculation of the principal remaining, i.e. the balance:

``````s = 1000
r = 0.1
d = 103.34306381837332

n = 36
p[n] = (d + (1 + r)^n (r s - d))/r = 0 as expected
``````

Plot of principal remaining over the 3 year term

`p[n] = (d + (1 + r)^n (r s - d))/r` for `n = 0` to `n = 36` Likewise for the calculation of the principal repayments:

Plot of principal repayments over the 3 year term

`pr[n] = (d - r s) (r + 1)^(n - 1)` for `n = 1` to `n = 36` The accumulated principal repayments after 36 months:

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

compared with total repayments of `36 d = 3720.35`.

Example amortisation table

``````month  interest   principal repayment =          accumulated     balance
n      at 10%     payment - interest repayment   princ. repmt.   p[n]
0                                                                1000
1      100        103.34306 - 100 = 3.34306        3.34306       996.657
2      99.6657    103.34306 - 99.6657 = 3.67737    7.02043       992.98
3      99.2979    103.34306 - 99.2979 = 4.04511    11.0655       988.934
...
35     17.9356    103.34306 - 17.9356 = 85.4075    906.052       93.9482
36     9.39482    103.34306 - 9.39482 = 93.9482    1000          0
``````

Derivation

The balance of a loan follows this recurrence equation.

``````p[n + 1] = p[n] (1 + r) - d
``````

where

``````p[n] is the balance of the loan in month n
r is the monthly interest rate
d is the regular monthly payment
``````

This can be solved like so (using Mathematica in this instance).

``````RSolve[{p[n + 1] == p[n] (1 + r) - d, p == s}, p[n], n]
``````

where `s is the initial loan principal`

yielding `p[n_] := (d + (1 + r)^n (r s - d))/r`

This notation expresses a formula for the balance in month n, which can be used in a function for the principal repayment `pr`, (that is, the regular repayment less the payment of interest on the previous month's balance).

``````pr[n_] := d - (p[n - 1] r)
``````

Combining these expressions produces an expression in terms of d, r, s & n.

``````pr[n_] := (d - r s) (r + 1)^(n - 1)
``````

After `n` periods the accumulated principal repaid is:

`accpr[n] = Σ(d - r s) (r + 1)^(k - 1)` for `k = 1` to `k = n`

∴ by induction, `accpr[n] = (d - r s) ((1 + r)^n - 1)/r`

The above results can be obtained more simply using the standard formula for the present value of an ordinary annuity, treating the remaining portion of the mortgage as a small loan itself.

For example, obtaining values for month 28.

``````s = 1000
r = 0.1
n = 36

P = r s/(1 - (1 + r)^-n) = 103.34306381837332
`````` The balance remaining in month 28

``````x = 36 - 28 = 8

balance = P(1 - (1 + r)^-x)/r = 551.328

principal paid = principal - balance = 448.672
``````

Which agrees with the previous formulation

``````accpr = 448.672
``````

and as Wick provides for Excel and Google Sheets

``````=CUMPRINC(0.1,36,1000,1,28,0)
``````
``````-448.672
``````

Mortgage amortization formulas

If:

`N = original length of loan, in periods` (such as 360 months = 30 years * 12 months / year)
`n = number of complete periods elapsed` (such as 0 at start of loan, or N after making last regularly scheduled payment)
`APR = annual percentage rate of loan` (without compounding)
`r = interest rate per period` (such as APR * 1 year / 12 months)
`P = principal of loan at time n`
`P0 = initial principal of loan` (at time n = 0)
`M = portion of monthly payment that goes toward principal and interest`

Then:

`u = N - n = number of periods remaining` (such as N at start of loan, or 0 after making last regularly scheduled payment)
`z = 1 + r` (compounding factor per period)
`P0 = M * (1 - z^(-N)) / r`
`P = M * (1 - z^(-u)) / r`
`. = P0 * (1 - z^(-u)) / (1 - z^(-N))`
`P0 - P = M * (z^(-u) - z^(-N)) / r`
`. = M * (z^(n-N) - z^(-N)) / r`
`. = M * (z^n - 1) * (z^(-N)) / r`

Notice that that number of periods remaining (`u`) decreases by 1 every period that you make your regularly scheduled payment.

How to derive the Interest Factors for single payments:

Suppose there is one period left on a mortgage. Assume that `z` is not equal to zero. Then:
`M = P * z`
`P = M / z`

Delaying every payment on a mortgage by 1 period reduces the initial value of the mortgage by a factor of `z`.

Delaying every payment on a mortgage by `u` periods reduces the initial value of the mortgage by a factor of `z^u`. In other words, it increases the initial value by a factor of `z^(-u)`.

`z^u` is known as the Future Value Interest Factor. If you let one dollar earn compound interest (at rate `r` per period), you will have `z^u` dollars after `u` periods.

`z^(-u)` is known as the Present Value Interest Factor. If you let `z^(-u)` dollars earn compound interest (at rate `r` per period), you will have one dollar after `u` periods.

How to derive the Present Value Interest Factor of an Annuity
(as used in the mortgage amortization formulas):

Suppose there is an "interest only" mortgage. The periodic payments are exactly enough to cover the interest, but the principal never changes. Then:
`M = P * r`
`P = M / r`

Suppose we split the payments on an "interest-only" mortgage into two parts: The first `u` payments, and all of the remaining payments.

Immediately after the uth payment is made, the value of "all the remaining payments" will be `P`. Thus, the initial value (at time `0`, before any of the `u` periods) of "all the remaining payments" is `P * z^(-u)`. Then:
`P = M / r =` initial value of the complete "interest-only mortgage"
`P * z^(-u) = M * z^(-u) / r =` initial value of "all the remaining payments"
`P - P * z^(-u) =` value of a mortgage that has `u` periods remaining
`. = M / r - M * z^(-u) / r`
`. = M * (1 - z^(-u)) / r`

Suppose instead of having `P` be the value of the complete "interest-only mortgage", we decide to have `P` be the value of a mortgage that has `u` periods remaining. Then (for the new definition of `P`):
`P = M * (1 - z^(-u)) / r`

Suppose we are at time `n = 0`, and `u = N`. Then the initial value of a mortgage with `N` periods is:
`P0 = M * (1 - z^(-N)) / r`

This formula is known as the Present Value Interest Factor of an Annuity.

Q.E.D.

• Could you tell me how you derived this? Thanks! – Sebastian Patten Dec 16 '16 at 23:32