Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Present Value Of A Perpetuity

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Present value is defined as K/i where K is the annual cash flow and i is the discount rate.

How would this formula change if the cash flow happens lets say evey 8 years in prepetuity (instead of every year). Thank you.


Similar Excel Video Tutorials

Helpful Excel Macros

Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return

Similar Topics







I have the following cash flows, each discounted at its corresponding discount rate, which gives me a 768.555 NPB.

Cash Flow Yrs Discount Rate Present Value
21.605 0,1 2,00% 21.562
21.605 0,6 2,12% 21.335
21.605 1,1 1,90% 21.161
723.605 1,6 1,69% 704.497
TOTAL 768.555

What i want to do is calculate the IRR which returns this present value given the cash flows.

What i've done is constuct the following cash flow
-768.555
21.605
21.605
21.605
723.605

Which corresponds to the estimated present value as an initial investment and then consider the other cash flows. Then i use excel IRR function which returns 0,67%. But if i discount each cash flow at that rate it doesnt return the calculated NPV (768.555). Using goal seek i get 0,99%. What's the problem, Is there someway to get to this yield without using goal seek?


Hello,

I'm working on a cash flow analysis, and I am looking for some help (hopefully not requiring a macro solution if possible).

In looking at a net cash flow analysis, I have a variable setup that says in X years, we will be looking to fully replace equipment with a value of Y. How can I get my cash flow to subtract that value depending on how many year we expect to we will need to replace the equipment?

For example-
(X) Replacement Cost: 100,000
(Y) Years: 3
Annual Estimated Net Cash Flow: 250,000

Year 0: -100,000 (initial equipment purchase)
Year 1: 250,000
Year 2: 250,000
Year 3: 150,000 (first replacement)
Year 4: 250,000
Year 5: 250,000
Year 6: 150,000 (second replacement)

Anyway, I'm hoping to make this dynamic so management can easily change the variables (except for cash flow which is inputted from another formula).

Thank you!
Brian


Hello,

I'm working on a cash flow analysis, and I am looking for some help (hopefully not requiring a macro solution of possible).

In looking at a net cash flow analysis, I have a variable setup that says in X years, we will be looking to fully replace equipment with a value of Y. How can I get my cash flow to subtract that value depending on how many year we expect to we will need to replace the equipment?

For example-
(X) Replacement Cost: 100,000
(Y) Years: 3
Annual Estimated Net Cash Flow: 250,000

Year 0: -100,000 (initial equipment purchase)
Year 1: 250,000
Year 2: 250,000
Year 3: 150,000 (first replacement)
Year 4: 250,000
Year 5: 250,000
Year 6: 150,000 (second replacement)

Anyway, I'm hoping to make this dynamic so management can easily change the variables (except for cash flow which is inputted from another formula).

Thank you!
Brian


I am attempting to find the rate of turn on the following - and I can't for the life of me figure out the formula - It is asking tha tyou take the value in cell 12 and the yearly cash flow values and then subtract the initial investment of 750,000. Any help would be great.
Cash Flow Summary Initial Investment Year 1 Year 2 Year 3 Year 4 Year 5 ($750,000) $274,643 $277,273 $307,715 $345,676 $388,873 Cumulative Net Cash Flow ($475,357) ($198,084) $109,631 $455,307 $844,180 Profitability of Investment Rate of Return 18% Net Present Value Internal Rate of Return


Hi all, This is my first post and it's a doozy! I'm working on creating an investment analysis spreadsheet and I need advice from those in the know.

I am trying to calculate cash flows to investors using variables that can be manipulated. I don't need any NPV or IRR stuff (That'll be done once this is solved)... Here's what I need:

1) for example,I've got 3 years of projected cash flow
Year 0 = (10,000)
Year 1 = 5,000;
Year 2 = 30,000
Year 3 = 25,000

2) Initially, the investors will get "x" percentage of the cash flow, once their initial investment * "y" occurs, then they will continue to receive "z" percentage of the cash flows in perpetuity

In the example above, let's say that "x" = 70%, "y" = 2, and "z" = 20%. In laymen's terms we are telling our investors that they will receive 70% of the cash flow until they double their money, and at that point they will continue to receive 20% of the revenue

Simple math tells us that in this example, the investors will double their money at 1.83 years... Then will receive 20% year 2 cash flow for .17 years, yielding an additional 1,020 in cash flow... Then year 3 is just a straight 20% for the entire year.

Is there a combination of formulas that I can use to be able to manipulate values "x", "y", and "z"? If so, would you be so kind to show me an example? Thank you in advance!

Cheers!

Bud

Hi guys..

i need some help in calculating the Discounted cash flow and Net present value for a project I made.... can someone please help me with it and create a sheet for these? in the "Assumptions centralized" sheet all the expenses and savings can be found. the project is for a 4 year period. discount rate 10%.

thx a lot in advance!


Hello friend! Please take a look to see if you could help.

I have an annual cash flow forecast as follows:

Total 2011 2012 2013 2014 2015 67,247,657 (16,897,525) 1,785,783 2,246,005 2,639,227 1,971,116 2016 2017 2018 2019 2020 2,955,283 3,159,493 2,806,202 3,476,776 3,646,009
2021 2022 2023 2024 3,822,240 3,157,590 4,191,510 48,287,946

The IRR calculated based on these cash flows is $19.22%. Then I broke these annual cash flows into quarterly cash flows by dividing each annual cash flow by 4 assuming cash inflows and outflows occur at the end of each quarter, and used the XIRR function to calculate the IRR based on the quarterly cash flows. The annualized quarterly IRR (18.93%) is surprisingly lower than the IRR based on the annual cash flows.

I thought the IRR calculated based on the quarterly cash flows should be higher than that calculated based on the annual cash flows because the quarterly cash flows are in and out throughout the year, rather than at the end of the year.

Can anyone please kindly help? The only reason I could think of is that the time value of money makes the cash outflow upfront matters most. In the annual cash flow schedule, the cash outflow occurs at the end of the year, while in the quarterly cash flow schedule, the cash outflow occur throughout the year. Also, the amount of the upfront cash outflow is significant.

Thanks in advance.

Megan


Hello friend! Please take a look to see if you could help.

I have an annual cash flow forecast as follows:

Total 67,247,657
2011 (16,897,525)
2012 1,785,783
2013 2,246,005
2014 2,639,227
2015 1,971,116
2016 2,955,283
2017 3,159,493
2018 2,806,202
2019 3,476,776
2020 3,646,009
2021 3,822,240
2022 3,157,590
2023 4,191,510
2024 48,287,946

The IRR calculated based on these cash flows is $19.22%. Then I broke these annual cash flows into quarterly cash flows by dividing each annual cash flow by 4 assuming cash inflows and outflows occur at the end of each quarter, and used the XIRR function to calculate the IRR based on the quarterly cash flows. The annualized quarterly IRR (18.93%) is surprisingly lower than the IRR based on the annual cash flows.

I thought the IRR calculated based on the quarterly cash flows should be higher than that calculated based on the annual cash flows because the quarterly cash flows are in and out throughout the year, rather than at the end of the year.

Can anyone please kindly help? The only reason I could think of is that the time value of money makes the cash outflow upfront matters most. In the annual cash flow schedule, the cash outflow occurs at the end of the year, while in the quarterly cash flow schedule, the cash outflow occur throughout the year. Also, the amount of the upfront cash outflow is significant.

Thanks in advance.

Megan




Hello there,

Please help me create a macro that calculates the present value of cash flows.

Please see my spreadsheet to understand .

For example Ins_1, only the first cash flow's present value should be calculated using the interest rate for Ins_1 and the difference in time between the Cash Flow Pay Date and the Maturity Date for Ins_1 (I have illustrated this in the spreadsheet).

For Ins_2 the first two cash flows need to be taken into account with the first cash flow's present value should be calculated using the interest rate for Ins_2 and the difference in time between the First Cash Flow Pay Date and the Maturity Date for Ins_2. For the second Cash flow, the present value should be calculated using the interest rate for Ins_2 and the difference in time between the Second Cash Flow Pay Date and the Maturity Date for Ins_2.

What I need this macro to do is to calculate the present value of cash flows in cells B2:B6 based on the dates in Cells A2:A6 e.g. For Ins_3 the cash flows that are taken into account for the calculation are those that are before the Maturity Date for Ins_3.

Thank you in advance.

Hi Everyone - can any of you help me with this question in my assignment?
I'd be very grateful for any assistance!

. Write a function named PValue(cfs, per, r) that returns the present value of a set of cash flows contained in the range cfs (cfs can be either a row or a column.). The range per contains the periods in which each cash flow occurs (e.g. the first cash flow in cfs occurs in period 2, the second cash flow is in period 5). The discount rate is r per period. Do not use any Excel functions within PValue().


I am bit confused about the %s used in MIRR function..

I am looking at a real estate investment.. Here is my cash flow situation

Year Cash Flow
0 -$33,638 (initial investment)
1 -$1330
2 -$1370
3 -$1416
4 -$1467
5 -$1524
6 -$1587
7 -$1656
8 -$1731
9 -$1814
10 $143,343 (projected sale proceeds)

I am using a 6% finance rate (for -ve cash flow) and 10% re-investment rate (for +ve cash flow).
I did MIRR(C0:C10, 6%, 10%) and got 12.54%

However, I increased my finance rate to 9 %, i.e. MIRR(MIRR(C0:C10, 9%, 10%) and got 12.88%

I would have thought if my finance rate for -ves cash flow is higher, then my MIRR rate will be lower. Am I using these % definitions rightly?


I have come up with a discrepency between two versions of a cash flow analysis that I am trying to do with the MIRR function. In both cases, the total in flow and outflows add up the same amount. The only difference is that one is done using 4 total flows, the first being the entry, and then 3 annual cash flows. The other is done using 37 total flows, the first being entry and the other 36 being monthly flows with the mirr rate being multiplied by 12 to get an annualized rate. The only negative flow is the entry.

They add up to the same sum of inflows and outflows, but the output from MIRR is off by 2% between the two versions, and strangely enough, its the annual version that has a higher percentage than the monthly version. I would have expected the annual to be lower since the inflow for the year would have been pushed further forward in time, thus discounting the cash flow more then when cashed out earlier.

I also ran the same cash flows with the NPV function and also get two different present values using annual and monthly versions, again the annual value being HIGHER than the monthly value by a significant amount.

I would just like to understand what is causing this discrepency and why the monthly version is producing a lower MIRR rate and lower NPV than the annual version, all other things being equal. Anyone have any ideas?


I am trying to do a return on equity analysis - could I get some help from someone please? I am a new analyst at a small boutique.

The initial investment is $6,000,000.
The free cash flows are as follows:
2016 = 600,000
2017 = 600,000
2018 = 600,000
2019 = 600,000
2020 = 600,000
2021 = 600,000
2022 = 600,000
2023 = 52,315,912
2024 = 64,588,052

The discount rate is 10%

The terminal value assigned to this project is 6x free cash flow.

The reason why the final two years of the projection period are so much higher is that in 2023, the debt is paid off and all free cash can flow down for equity. (ie no debt service burden)

I know I should do an IRR calculation with -6,000,000 as initial cash flow, but confused about how to integrate the terminal value (6x FCF) and the discount rate of 10%.

Could someone give me some guidance asap in doing this calculation in excel? Thank you so much.


I have set up a spreadsheet to calculate the payback period on an investment

The payback period is the year in which the cumulative cash flow first becomes positive. In the example below it becomes postive in year 6. You then take the cumulative cash flow in the year it first becomes positive i.e 8.02 and divide this by the correspoding cash flow 22.55 = 6+ 8.02/22.55 = 6.36 years

I need a formula that will automatically calculating the simple payback cell E6. As soon as the cumulatve cash flow becomes positive, the year must be looked up and the first cumulative cash flow that it postive must be divided by the cash flow to the left of it

See my sample data below

Your assistance in this regard will be most appreciated

Sheet1

* A B C D E 1 Year 1 cf 14 * * Payback Using Match 2 Growth 0.1 * * 6 3 Initial investment 100 * * * 4 Year Cash flow Cum cash flow * Simple Payback 5 0 -100.00 -100.00 * 6.36 6 1 14.00 -86.00 * * 7 2 15.40 -70.60 * * 8 3 16.94 -53.66 * * 9 4 18.63 -35.03 * * 10 5 20.50 -14.53 * * 11 6 22.55 8.02 * * 12 7 24.80 32.82 * * 13 8 27.28 60.10 * * 14 9 30.01 90.11 * * 15 10 33.01 123.12 * * 16 11 36.31 159.44 * * 17 12 39.94 199.38 * * 18 13 43.94 243.32 * * 19 14 48.33 291.65 * * 20 15 53.16 344.81 * *
Spreadsheet Formulas Cell Formula E2 =MATCH(0,C5:C20,1) B5 =-Initial_investment C5 =B5 E5 =+A11+(C11/B11) B6 =Year_1_cf C6 =C5+B6 B7 =+B6*(1+Growth) C7 =C6+B7 B8 =+B7*(1+Growth) C8 =C7+B8 B9 =+B8*(1+Growth) C9 =C8+B9 B10 =+B9*(1+Growth) C10 =C9+B10 B11 =+B10*(1+Growth) C11 =C10+B11 B12 =+B11*(1+Growth) C12 =C11+B12 B13 =+B12*(1+Growth) C13 =C12+B13 B14 =+B13*(1+Growth) C14 =C13+B14 B15 =+B14*(1+Growth) C15 =C14+B15 B16 =+B15*(1+Growth) C16 =C15+B16 B17 =+B16*(1+Growth) C17 =C16+B17 B18 =+B17*(1+Growth) C18 =C17+B18 B19 =+B18*(1+Growth) C19 =C18+B19 B20 =+B19*(1+Growth) C20 =C19+B20
Excel tables to the web - Excel Jeanie Html 4





I have using the mtch fo




Hi there everyone! This is my problem:

Given the following information for the following indivisible and independent projects (where c= cashflow):

c0 c1 c2 c3
A -200 200 -90 240
B -250 200 -40 250
C -300 100 400 -60
D -50 -400 350 390
E 300 200 200 -870
F -235 180 -150 390

and the following constraints:
1. Cost (Cash flow) must be >-550
2. Cash flow in period one must be >200
3. Cash flow in period three must be >-400
4 Cash inflow in period two must be >300 and <500

Without the use of solver, which combinations of projects will yield the highest NPV, if the required rate of return is 10%?
The invester can choose as many projects as the funding and cash flow will allow.

Dear Sir,



I am requesting your help in reviewing the base of how to calculate IRR for the cash flow beyond the projected period, is this the best way or there is another method to calculate that cash flow

Last cah flow projected/the discount rate

I tried to attached file but it is not possible here


10% 100% 91% 83% 75% 68% 62% 56%

1/1/2008 1/1/2009 1/1/2010 1/1/2011 1/1/2012 1/1/2013 1/1/2014
Cash Flow -0.024 -11.2 -35.8 -1.6 25.0 25.0 25.0
Discounted Cash -0.024 -10.2 -29.6 -1.2 17.1 15.5 14.1

IRR 14.65%
NPV 5.715518989

Cash Flow 0 0.0 0.0 -54.6 25.0 25.0 25.0
Discounted cash 0 0.0 0.0 -41.0 17.1 15.5 14.1

IRR 17.75%
NPV 5.715552047





-0.031944 -13.552 -39.38 -1.6



Hello, I'm trying to measure two sets of cash flows. Both are essentially the same except with the second, I am forcing four years of cash outflows into one year, a new time 0 if you will. Below, I am discounting it back to 2008. So the -54.6 = the sum of the four numbers right above the text = -.024*1.1 + -11.2 * 1.1^2 + ...

In either case, my NPV is the same, but my IRRs are different. What am I missing?

Thanks in advance,




Michael


Hi All,

I've been battling the use of XNPV trying to reconcile the result I am getting between a Nominal cash flow and Real cash flow.

Normally I would have my periods in years and use NPV on both the Real and Nominal models with no problem. This is pretty much the first time I've used XNPV.

The problem is that the XNPV result that I was getting from my Real and Nominal models is different. After trying to find the error in my model I moved to looking at XNPV and the result it was giving me. I have pulled together an example that hopefully describes my problem adequately.

Firstly I created a one year time period in months, put in a real cash flow line, escalated by inflation (divided by 12) to get my nominal cash flow. Using XNPV I get a Real NPV of 543.378 and a Nominal NPV of 545.014.

At first I thought this might be something to do with the way I was using XNPV, so next I set the time line to be 12 years in yearly periods, and adjusted the Nominal cash flow accordingly (not divided by 12). The result produced a Real NPV of 121.85 and a Nominal NPV of 125.43.

Finally, as a sanity check I did a straight NPV of the annual cash flows and I get and NPV of 110.98 for both the Real and Nominal cash flows.

Hopefully this makes sense and is easy to follow...

Am I making a fundamental mistake? or is the XNPV function always like this?


OK excel guys... I hope this is an easy one! Here we go...

I have a simple bar chart. The x-axis labels give me a range of values from 1 to 10 (ex: a ten year cash flow). I want to make this variable so if I choose to look at the chart using only a 5 year cash flow, I'm not looking at a chart that has data charted and truncating in year 5 with half the chart taking up empty space. Likewise, if I wanted to look at a 20 year cash flow, it would expand out to 20 years instead of being limited to 10 years, as defined by the range initially selected.

I'm using the "Source Data | Category (x) axis labels" field to redefine the range at present. Is there a way to make this dynamic through a cell reference (the referenced cell builds the range reference), or through a macro? I think a macro would work, but I'm trying to keep this "so easy a caveman could do it" if you know what I mean.

Thanks.


Hi, how would I calculate the net present values for each discount rate in D9:D28 with the values coming from B5:B26 (this range represents the cash flow for each year), assuming that the initial investment occurs not immediately but in a year? Thanks


I have the following expression in my form.

=([Cash]+[Investments]+Forms![Borrowing Base Form]![Excess Availability])/[Free Cash Flow]*-1

"Free Cash Flow" can be a positive number or negative number. If free cash flow is a negative number, I have it set up so the answer is a positive number (hence the *-1). which is what I want.

If "free cash flow" is a positive number I don't need the calculation because it is meaningless. So if "free cash flow" is a positive number I want the answer to be "N/A"

What this tells me is how long a company can hold out before it runs out of cash. Free Cash flow if negative means they are burning thru cash to stay in business. If free cash flow is positive then they have cash coming in to stay in business.

Is there a way to do this?


A friend wants to find out the present value of an asset he owns that will generate $5million over next 25 years.
I am assuming that it will be at the rate of $200K p.a.
For a discount rate of 10% what is the formula to calculate the present price that he should accept.
Thanks
Pedro


Hi,

I'm new to excel and have been asked to model a rather complex investment structure. If anyone could help me with this I would be forever grateful. The cash flows are distributed between the limited partner (LP) and the managing partner (MP). There are three hurdles though with different divisions of cash for each. The structure is as follows

For cash flows generating an IRR less than 15%
LP gets 75%
MP gets 25%

For cash flows generating between IRR 15%-20%
LP gets 50%
MP gets 50%

For Revenue reaching over 20%
LP gets 25%
MP gets 75%

So I have my NPV cash flows laid out and have calculated the IRR for each year but can't visualize how to calculate the relevant sums of money for each cash flow. For example, the amount of cash that generates a 15% IRR in year 3 will be different than the amount of revenue that generates a 15% IRR in year 4.

So say there is cash flow for a year that generates a 30% IRR. I would need to first split the cash into three separate sums. The first would be the cash that generates an IRR of up to 15%, then another sum of the cash on top of that that generates between 15-20% IRR, then all the cash over 20%. The sum of these three chunks will equal the total cash flow for given year. Then I can apply the relevant structure to each cash sum (LP gets 75% of first sum, 50% of second sum, and 25% of third sum)

I need to split up the cash flows into the three different groups based on the hurdles so I can apply the correct structure to the relevant part of the cash flow.

Can I get excel to spit out the relevant sums of money for each hurdle level for each year?

Sorry if this is not clear, I am completely new to this and will elaborate if need be.

Thanks,

Colin


Hey guys,

Stumped on an assignment question which is as follows:
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"> Write a function named PValue(cfs, per, r) that returns the present value of a set of cash flows contained in the range cfs (cfs can be either a row or a column.). The range per contains the periods in which each cash flow occurs (e.g. the first cash flow in cfs occurs in period 2, the second cash flow is in period 5). The discount rate is r per period. Do not use any Excel functions within PValue().

I know how to write a function to calculate present value when there are even time periods. But the time periods for us go: 1, 3, 6, 7. So basically I want to extract each one and divide each cashflow by 1+10% to the power of its matching period, and then sum them all up.

So far I have tried this function but it is not giving me the correct answer:

Function PValue(Cfs, per, R)

For i = 1 To Cfs.Cells.Count
For j = 1 To per.Cells.Count
cum = cum + (Cfs.Cells(i) / (1 + R) ^ (per.Cells(j)))

Next j
Next i

PValue = cum

End Function

Any suggestions would be greatly appreciated!


Hi there -

I'm trying to determine how to calculate two things in a business model, and need some formula help - the company has $X in cash today, and I need (1) to automate display of the month/year when current cash runs to zero, and then (2) the total sum from the zero point in step 1 to the cash flow positive point.

On one row J41 through BL41 is the cumulative cash flow total; I am moving around costs/revenues fluidly, so wondering how/if these two items listed above can be automated in display.

Thanks!
Brian