Formula for perpetuity with cash flow every 3 years

[legendary]

I am trying to calculate the present value of a perpetuity with an inflow of cash ($10,000) every 3 years.

Any help can be provided?

Thanks alot.

 

[Firefly2012]

Hi & Welcome to the Board!

Would you mind clarifying exactly what the cash flows are? Do you mean the only cash flows are an inflow of $10k every third year? If so, what discount rate are you using to value these?

 

[legendary]

Hi,

I am currently doing a project valuation.
The company injects non-depreciable capital every 3 years to fund the project. The first injection of $10,000 will occur in year 3 and subsequent injections will grow at a rate of 10% p.a.

Hence, I need to find the perpetuity on Excel with the mentioned cash flow. (Every third year - 10k with increase of 10% p.a)

Any help will be very much appreciated. Thanks!

 

[Firefly2012]

I'm still not clear when the cash flows occur I'm afraid - would you mind preparing a small table (you can do it in an excel sheet, apply a border and then simly copy and paste into your reply) showing tenor (ie year) in the left column and cash flow in the right column. Please start at year zero and increment by 1 year every row. It will help me or anyone else immensely in determing a solution.

You will need to provide your discount rate as well (otherwise you can't value the cash flows). You may know this as your cost of capital, your funding cost, your risk free rate, interest rate or some other designation.

 

[legendary]

Hi,

Thanks for the prompt reply.

The discount rate (or required rate of return) is 8%.
I have attached the table of values below. The capital injections occurred every 3 years, but it grows at a rate of 10% p.a.
I am trying to find the perpetuity of the injection.

[TABLE="width: 195"]
<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 65"]Years[/TD]
[TD="class: xl64, width: 65"] [/TD]
[TD="class: xl65, width: 65"] [/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]0[/TD]
[TD][/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]1[/TD]
[TD][/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2[/TD]
[TD][/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3[/TD]
[TD="class: xl68, align: right"]$10,000.00[/TD]
[TD="class: xl70, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]4[/TD]
[TD="class: xl69, align: right"][/TD]
[TD="class: xl70, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]5[/TD]
[TD="class: xl69, align: right"][/TD]
[TD="class: xl70, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]6[/TD]
[TD="class: xl69, align: right"]$13,310.00[/TD]
[TD="class: xl70, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]7[/TD]
[TD="class: xl69, align: right"][/TD]
[TD="class: xl70, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8[/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl70, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9[/TD]
[TD="class: xl69, align: right"]$17,715.61[/TD]
[TD="class: xl70, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]10[/TD]
[TD="class: xl69, align: right"][/TD]
[TD="class: xl70, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]11[/TD]
[TD="class: xl69, align: right"][/TD]
[TD="class: xl70, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]12[/TD]
[TD="class: xl69, align: right"]$23,579.48[/TD]
[TD="class: xl70, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]13[/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl70, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]14[/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl70, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl71, align: right"]15[/TD]
[TD="class: xl72, align: right"]$31,384.28[/TD]
[TD="class: xl70, align: right"]10%[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

Any help will be very much appreciated! Thanks

 

[Firefly2012]

Hi

Thanks for that - explained what you are doing very clearly.

Unfortunately, there is no convergenace given the figures you quote and since the growth rate (10%pa) exceeds the discount rate (8% pa) the sum to infinity of the series is infinity itself (ie unquantifiable). You would need to provide a finite number of years over which you wish to calculate the cost (so on the basis of an annuity rather than a perpetuity).

 

[legendary]

Hi,

I have tweaked the figures, as I am trying to find out the formula for a perpetuity.
The discount rate is now 9%. The growth rate is 5% and the cash flow occurs every 5 years.

[TABLE="width: 198"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Years[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]$150,000.00[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]$191,442.23[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]$244,334.19[/TD]
[TD="align: right"]5%[/TD]
[/TR]
</tbody>[/TABLE]


Trying to find out the excel formula.
Any help will be appreciated. Thanks!

 

[Firefly2012]

OK the way I would approach this is:

1. You effectively have a discount rate (taking into account both the interest rate and the growth rate) of:

=(1+5%)/(1+8%)
=0.972222

per year. Since you have a period of 5 years, you can just raise this to the power of 5 to determine your discount rate for each 5 year period:

=((1+5%)/(1+8%))^5
=0.868616

2. The sum to inifinity of a geometric series (your perpetuity) is:

a/(1-r)

Here, a is 150000 discounted by the interest rate (8%) compounded for 5 years (as first cash flow is in 5 years' time). r is the 5 year discount factor (0.868616)

=(150000*1/(1+8%)^5)/(1-((1+5%)/(1+8%))^5)

=$777,015