Compute the 25 Annual End-of-Year Payments Based on Whatever You Need to Retire
Analysis
The PMT function can be used to calculate an annuity contribution. Here’s the formula:
=PMT(C6,C7,C4,C5,0)
Talking Points
The PMT function is a financial function that returns the periodic contribution amount for an annuity. Given the annuity amount, number of periods, and interest rate, the PMT function can be used to calculate the annuity contribution. An annuity consists of a series of evenly spaced and equal cash flows. The objective of this example is to accumulate one hundred thousand dollars after ten years by earning five percent annually. Each year, dividends are reinvested at the end of the year. Here’s the formula:
=PMT(C6,C7,C4,C5,0)
where:
rate – from cell C6, 6%.
nper – from cell C7, 25.
pv – from cell C4, 0.
fv – from cell C5, 698000.
type – 0, end-of-period payment (regular annuity).
The PMT function returns -$54.680.12 with these parameters. The negative value indicates a cash outflow.
Annuity
In this example, contributions are made at the start of the period. To calculate a contribution at the beginning of the year, use 1 for the type argument. Here’s the formula:
=PMT(C6,C7,C4,C5,1)
resulting in a contribution amount of -$51,858.05. Type = 1 is the only difference in this formula.
——————————————————————————————————-
Annuity
The formula for an annuity depends on the type of annuity. Here are the formulas for the most common types:
Future Value of an Annuity:
The future value of an annuity formula is used to calculate the future value of a series of regular payments. The formula is:
FV = Pmt x [(1 + r)^n – 1] / r
Where:
FV = Future Value of Annuity
Pmt = Payment Amount
r = Interest Rate per Period
n = Number of Periods
Present Value of an Annuity:
The present value of an annuity formula is used to calculate the present value of a series of regular payments. The formula is:
PV = Pmt x [1 – (1 + r)^-n] / r
Where:
PV = Present Value of Annuity
Pmt = Payment Amount
r = Interest Rate per Period
n = Number of Periods
Annuity Payment:
The annuity payment formula is used to calculate the regular payment required to achieve a specific future value or present value. The formula is:
Pmt = PV x r / [1 – (1 + r)^-n]
or
Pmt = FV x [r / (1 – (1 + r)^-n)]
Where:
Pmt = Payment Amount
PV = Present Value of Annuity
FV = Future Value of Annuity
r = Interest Rate per Period
n = Number of Periods
B-861-Finance
