1

Using the PMT formula in Excel, I can calculate the monthly payments based on the interest on the loan (e.g.2%), the principal value of the loan (e.g. £100,000), and the term of the loan (e.g. 25 years).

Is there an equivalent formula, to take the monthly payments, the principal and the percentage; and output the term for the loan?

In other words, could someone rearrange the equation at https://en.wikipedia.org/wiki/Compound_interest#Monthly_amortized_loan_or_mortgage_payments for me, so that n is assigned instead of c?

3

In excel use the nper() formula:

Description

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. Syntax

NPER(rate,pmt,pv,[fv],[type])

For a more complete description of the arguments in NPER and for more information about annuity functions, see PV.

The NPER function syntax has the following arguments:

  • Rate Required. The interest rate per period.

  • Pmt Required. The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes.

  • Pv Required. The present value, or the lump-sum amount that a series of future payments is worth right now.

  • Fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

  • Type Optional. The number 0 or 1 and indicates when payments are due.

for your example:

 =PMT(2%/12,25*12,100000) or 423.85 per month
 =NPER(2%/12,423.85,-100000) or 300.0004 months
| improve this answer | |
1

Starting with the formula for the present value of an ordinary annuity: enter image description here

the rearrangement is straightforward. Unfortunately, this site does not welcome the input of mathematical formulas, so by verbal description

  1. Divide both sides by C
  2. Multiply both sides by i
  3. Subtract 1 from both sides
  4. Change the sign of both sides
  5. Take logarithms of both sides
  6. Divide both sides by log(1 + i)
  7. Change the sign of both sides and we are left with:

n = - (log (1 - PV x i / C) / log(1 +i ))

In general, this will not lead to an integer solution.

Your options then would be to use the closest integer and adjust either the last payment, the principal amount, or the regular payment.

EDIT: This site: https://brownmath.com/bsci/loan.htm#LoanNumber shows the formula, with different symbols, properly type-set

enter image description here

| improve this answer | |

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.