Financial
Sinking Fund Payment (PMT) & Growing Perpetuity Valuation CF1 / (r - g) formulas
Calculate periodic sinking-fund savings needed to retire a future bond/capex obligation via PMT(r, n, 0, -FV) and value infinite cash flows via CF1 / (r - g).
Two constant-rate cash-flow models
The fund starts at zero and receives equal deposits at each year end, including the target date. B3 is an assumed effective annual return, not a guaranteed investment yield. Taxes, fees and varying returns are outside this model. PMT returns the unrounded payment; rounding every deposit to cents can leave a small final surplus or shortfall. Adjust the final deposit using an actual balance schedule.
The perpetuity starts with next year’s cash flow CF1, not the current year’s cash flow. Annual growth and discount rates stay constant forever. This exercise requires a nonnegative CF1, both rates greater than -100%, and growth strictly below the discount rate. It rejects r <= g even for a zero cash-flow input as an explicit model-domain policy.
Use consistent cash-flow and discount-rate definitions, currency and nominal or real basis. A finite calculation is not evidence that perpetual growth or the chosen discount rate is realistic; the output is especially sensitive when the rate gap is small.
| Treasury Model | Inputs | Formula | Model result |
|---|---|---|---|
| Sinking Fund Target FV | B2 = $500,000 | B3 = 6% rate, B4 = 10 yrs | PMT(6%, 10, 0, -500000) |
| Annual sinking deposit | $37,933.98 rounded | 10 rounded deposits = $379,339.80 | Final balance may differ slightly from $500,000 |
| Growing Perpetuity | CF1 B5 = $60k, r B6 = 10%, g B7 = 4% | 60,000 / (0.10 - 0.04) | $1,000,000 Present Value |
| Constant Perpetuity (g = 0) | CF1 = $60k, r = 10% | 60,000 / 0.10 | $600,000 Present Value |
Annual Sinking Fund deposit formula (PMT)
A negative future value and zero initial balance return a positive deposit. The omitted timing argument means end of period. B4 must be a positive whole number of years, B2 nonnegative, and B3 greater than -100%. A zero return gives target divided by years. Rates and period counts must use matching units.
=IF(COUNT(B2:B4)<>3,"Invalid inputs",IF(OR(B2<0,B3<=-1,B4<1,B4<>INT(B4)),"Invalid inputs",ROUND(PMT(B3,B4,0,-B2),2)))Guarded Growing Perpetuity Present Value CF1 / (r - g)
Validate numeric inputs and the model domain before dividing by the discount-rate gap. Declining cash flows are allowed when growth is negative but greater than -100%.
=IF(COUNT(B5:B7)<>3,"Invalid inputs",IF(OR(B5<0,B6<=-1,B7<=-1),"Invalid inputs",IF(B6<=B7,"Invalid (r <= g)",ROUND(B5/(B6-B7),2))))Try the example
Use the original values or change them to test your understanding.