Calculate term of mortgage based on monthly payment value
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 en.wikipedia.org/wiki/Compound_interest#Monthly_amortized_loan_or_mortgage_payments for me, so that n is assigned instead of c?
(2)2 Comments
Sorted by latest first Latest Oldest Best
Starting with the formula for the present value of an ordinary annuity:
the rearrangement is straightforward. Unfortunately, this site does not welcome the input of mathematical formulas, so by verbal description
Divide both sides by C
Multiply both sides by i
Subtract 1 from both sides
Change the sign of both sides
Take logarithms of both sides
Divide both sides by log(1 + i)
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: brownmath.com/bsci/loan.htm#LoanNumber shows the formula, with different symbols, properly type-set
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
Terms of Use Privacy policy Contact About Cancellation policy © freshhoot.com2025 All Rights reserved.