Help with annuity formula

**kkundra** · 07-03-2013, 10:23 AM

Hi,
I need some help with an annuity formula. I'm reading about PV, FV, PMT, etc but none seem to do what I want (I think).

I'd like to deposit money into an account today that will earn interest. Money will be taken out of the account on monthy basis for roughly 14 years. This monthly amount must be the same taking inflation into account over those 14 years.

So I'd like to input the monthly amount, an estimated earned interest rate, and an estimated inflation rate (which is about 3.3% annually as far as I'm aware correct?).

Any suggestions?

-Kris

**Pauleyb** · 07-03-2013, 11:21 AM

You probably want NPV, but I don't know what output you are looking for. The initial deposit? The length of time before money runs out? Note that in some cases, it is easier to not rely on a single formula, but a table of formulas (i.e. a row for each month). For NPV, your rate would be the interest rate, and the 'values' after that would be an array of the monthly payments that you have increasing due to inflation.

MoneyMaker · 07-03-2013, 11:29 AM

I mean I can answer your question but it seems like my previous two replies have been removed by some Mod

So not sure if anything I leave here will stay intact

MoneyMaker · 07-03-2013, 11:37 AM

In any case what you are trying to find is the capital recovery amount

You are depositing an amount which is the PV (present value)
The amount PV will earn interest rate RATE for NPER number of periods and will inflate at a GRADIENT
You are seeking to find an initial payment that you can withdraw each month from your account whilst all funds are depleted at the end of 14 years. This payment will inflate at the GRADIENT

Excel has a PMT function that finds a uniform series of payment yet it can not handle a GRADIENT

Here is one solution if you feel like using it http://tadxl.com/excel_pmt_function.html