3

In this case, the mortgage constant (or loan constant or debt constant) is the (in my case, annual) ratio of constant payments to the original amount, like here: http://www.double-entry-bookkeeping.com/periodic-payment/how-to-calculate-a-debt-constant/

Suppose we have an annual interest rate of 4.565% and 360 payments (30 year loan). In Excel, we can specify the following formula:

PMT(0.04565/12, 360, -1, 0, 1) * 12

Where present value is $1, future value is $0, and Type=1 signifies that payments are due at the beginning of the period. The result is 6.1034%.

When I apply the mathematical formula, it assumes that payments are made at the end of the period (or am I wrong?), so where

Debt Constant = (Interest Rate/12) / (1 - (1 / (1 + Interest Rate/12))^n) * 12
              = (0.04565/12) / (1 - (1 / (1 + 0.04565/12))^360) * 12
              = 6.1267%

How is the formula adjusted to account for the payments made in the beginning of the period, since the actual value for the purposes of this calculation is not known? Or am I misunderstanding something?

Thanks in advance.

  • This is a question about accounting not an issue of personal finance that the OP is facing. – Dilip Sarwate Feb 11 '18 at 18:20
  • @Dilip Sarwate Apologies if this is completely off-topic, a Google search for questions about mortgage constants led me here, so I posed my own since those didn't answer my specific concern. I believe the Accounting forum never took off--do you know of a more appropriate place to post this? – user2524282 Feb 11 '18 at 19:06
  • I just asked a meta question about this closure: money.meta.stackexchange.com/q/2539/10997 – Ben Miller - Reinstate Monica Feb 12 '18 at 12:50
  • 5
    Figuring out basic mortgage calculations seem to be perfectly in the personal finance area of accounting. – Chris Degnen Feb 12 '18 at 12:54
3

Short answer

Your mathematical formula can be adjusted by dividing by (1 + Interest Rate/12), i.e.

Debt Constant =
(0.04565/12)/(1 - (1/(1 + 0.04565/12))^360)*12/(1 + 0.04565/12) =
0.0610344

Long answer

The syntax for the Excel formula is

PMT(rate, nper, pv, [fv], [type])

Ref. Excel PMT function

type = 1 is for payments at the beginning of the period, so you are calculating the payments for an annuity due.

PMT(0.04565/12, 360, -1, 0, 1) * 12 = 0.0610344

Your mathematical formula is for an ordinary annuity; payments made at the end of the period.

Related info: Calculating The Present And Future Value Of Annuities

Formula for an annuity due (payments at the beginning of the period)

With principal s, n periods, periodic rate r and periodic payment d

s = 1
n = 360
r = 0.04565/12

The principal equals the sum of the payments discounted to present value.

enter image description here

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

12 d = 0.0610344

Derivation of formula

Using the geometric sum theorm

https://en.wikipedia.org/wiki/Geometric_series#Formula

enter image description here

1

Suppose you started with the formula for payments at the beginning of the period, and wanted to know how to adjust it for payment at the end. Well, each payment is accruing interest over an entire period. So you would have to multiply each payment by the interest factor for each period. The interest rate over a period is the interest rate per year divided by the number of periods per year. Since there are 12 period each year, the interest rate per period is 0.04565 (the interest given on a yearly basis) divided by 12. The total amount is the principal plus the interest rate times the principal:

principal + (interest rate)*principal

Factor out the principal, and you get:

principal*(1+interest rate)

So we get that the factor is 1+interest rate = 1+0.04565/12

That is the factor we have to multiply by to get from "beginning of period" to "end of period", so we have to divide by that to go the other way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.