Sleeper: Discount Cash Flow

[mdmackillop]

I know this is not a vba question, but I'll try anyway.
I'm trying to convince myself that I'm getting the correct answer from DCF functions. Basically, I have varying amounts to be paid out in repairs over a 12 year period. I need to find the sum of money to be paid into an account to pay all these items. The amount paid in will increase annually with inflation, and of course the repair costs are also affected by inflation. The attached spreadsheet should calculate the NPV of the expenditure, and the Annual Equivalent of that. The lower half of the table sums the payments to be made based upon this AE figure. I would expect the final balance to be zero, but it doesn't work out that way.
The actual project will involve 100+ items over a 60 year span, so this is "test" model only.
(Apologies in advance for the Range Names used to identify single cells )

[Aaron Blood]

Wow, a DCF question...

I looked at the example briefly, decided I hated it for some reason...

Gimme a few minutes and I'll post back with a similar analysis in a format that makes sense to me. Then we'll talk.

[Aaron Blood]

OK... Here's how I look at it.

Does it answer your question or did you want somfin else?

(forgive the $ in place of the pound mod, just not a handy symbol for me)

[brettdj]

The problem is that the lumpy nature of the expenditure means that an annuity investment may not be appropraite.

• The Excel NPV equation assumes cashflows occur end of period, but when you look at a budget its best to asssume that cashflows are mid period. This can be done three ways, use manually discount factors (ie use 0.5, 1.5 in A45, A46 etc note 1.0, 2.0), multiply the NPV by (1+discount rate)^0.5, or use XNPV which takes a date range
• Be consistent in your cashflow basis. You have discounted real cashflows in C45:C56, so your disount rate of 5.0% must also be in real terms (ie return excluding inflation)

1. Calculate the real PV of the expenditure
2. Calculate the real annuity require to generare this PV after 12 years. Use goalseelk
3. Check that the annuity is always enough to satisfy the cash calls

I'll pull togther an example
Goddam range names leading to blank cells

[brettdj]

Example attached. The example shows the three ways to calc mid point NPV

The model assumed

1. Investments take place at the start of a period
2. Expenditures are mid period
3. All PV's are real
4. Final cashflows include CPI

I use range names for the goal seek ....

[mdmackillop]

Thanks a lot both. I'll check these out at the weekend.

[mdmackillop]

My head is still spinning, but I'm starting to follow it (even if I don't understand it!) A couple of questions

Aaron, Should the PV calculations in Rows 30 - 33 not be based on the Real values in Rows 12 - 15 rather than the Nominal values?

Dave, Should the percentage in Row 52 not be 105% ie 1 + Discount rate.
What does CPI stand for?

Thanks for your efforts to educate me.
Malcolm

[brettdj]

Hi Malcolm,

CPI is the consumer price index.

So if your return was 5% (exlcuing inflation) and then inflation or CPI was 6% then the total return in nominal terms is
(1+5%)*(1+6%) = 111.3%
the equivalent real return is
(1+5%) = 105%

Investments are almost always made on a nominal basis.

Cheers

Dave

[Aaron Blood]

Aaron, Should the PV calculations in Rows 30 - 33 not be based on the Real values in Rows 12 - 15 rathewr than the Nominal values?

The PV calcs are correct. In my version of the model, they DO point to the nominal amounts (row 30 refers to row 18 for instance). The sum of the discounted (real) dollars is the PV of the stream of cashflows.

I was merely showing if you escalate a series of values at 5% and then discount them back at that same rate (as I thought you were trying to do in your example), you end up right back where you started if you're doing the math right.

Just the basics on how the FV/PV math is s'posed to work.