calculate NPV of increasing rents with varying multipliers

[koolchris99]

Morning,

In short i have a huge list of leases lease's which all have varying terms etc, but the one im having trouble with is the rising/doubling rents and their NPV, Ill use one lease as an example:

lease start 01/01/2001
lease length 99
lease doubles every 25 years.
Initial rent £175

So I can work out the next rent review using =IF(AF3="","no RR",EDATE(AC3,CEILING(DATEDIF(AC3,TODAY(),"y")+1,AF3)*12))
where :
AF3 = multiplier period, i.e 25 years
AC3 = Lease start

Using the above how would i calculate the full NPV for this lease? i know a 2 stage lease with fixed rents i can do:

ROUND(PV(interestrate,stagelength,(rent*2))/(1+interestrate)^yearsdelayed,decplaces)

But how would i extend that to do my multi stage increasing rent lease as above!! its tricky

Cheers

Chris

 

[xenou]

I don't know of any easy way, I'm afraid. You'd need to calculate the PV for four cash flows, starting in year 0, 25, 50, and 75. Then take the results for the last three and convert them to PV amounts in year 0. I don't see an interest rate, but if we say 10% then:


PV of twenty-five  175GBP payments:  1,588.48GBP  (year 0) =>  1,588.00GBP in year 0 PV terms
PV of twenty-five  350GBP payments:  3,176.96GBP (year 25) =>    293.22GBP in year 0 PV terms 
PV of twenty-five  700GBP payments:  6,353.93GBP (year 50) =>     54.13GBP in year 0 PV terms
PV of twenty-five 1400GBP payments: 12,707.86GBP (year 75) =>      9.99GBP in year 0 PV terms
---------------------------------------------------------------------------------------------
total of all cash flows in year zero PV terms:                 1,945.34GBP today

 

[xenou]

Actually whether it's easy or not depends on what you mean by a huge list, and how much variation you have in the different cases - i.e., whether you can build a template or not.
ξ