Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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.

View Answers     

Similar Excel Tutorials

PV Function - Get the Present Value in Excel
The Present Value (PV) function in Excel will return the current value of an investment.  This calculates the curr ...
RATE Function - Calculate an Interest Rate in Excel
The Rate function in Excel (RATE) calculates the interest rate for a financial transaction, such as for an annuity ...
FV Function - Get the Future Value in Excel
The Future Value function (FV) in Excel will return the future value of an investment based on a particular intere ...
NPER Function - Calculate Number of Periods Needed for a Set of Payments in Excel
How to calculate the number of periods required for an investment in order to get the desired return.  The number o ...

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
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
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

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?




Dear all,

Good day.

Kindly see my attached file, just wondering is there a formula or any way that could calculate the present value for year 4 - 8 (as shown as ?? in the Template, which should total 14,199.85, based on the Formula Proofing) instead of breaking the years into each individual columns, which would end up showing the correct value.
Reason being I have a cash flow that could last up to 30 years and it would be tedious to expand those.

There are 2 elements in this cash flow:
1) Income for 1st 3 years is based on actual no. and from 4th year onwards, it's a 10% constant growth;
2) Income for 1st 3 years is based on actual no. and from 4th year onwards, it's a 10% of sales.

The second table below shows the expected correct no. if they're broken down into individual columns.
Many thanks in advance!

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


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


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


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

I've got a circular reference problem.

I have a cash flow statement that has a funds portion on the bottom.
I push the operating cash flow into the funds section to then calculate
interest income which becomes part of the cash flow statement.

Example

Interest Income - Cell b11 - This becomes part of total revenue
which feeds into the cash flow total positive or negative.

The fund section starts with a fixed number and then either increases
or decreases based on the cash flow positive or negative

Example

Fund Balance 10,000 - cell b53
Cash Flow 1,000 - cell b55

The interest income would be 23 if we assume a 2.5% interest rate.
If the calculation drops into cell b11 which would make the total of
b53 & b55 produce 11,023 which the formula would then recalculate on.


Hello - I have the following summary table in a spreadsheet:

Date Amount Cash Flow #1 9/1/2010 $1.00 Cash Flow #2 12/15/2010 $1.00 Cash Flow #3 3/15/2011 $1.00 Cash Flow #4 6/15/2011 $1.00 Cash Flow #5 9/15/2011 $1.00

Then I have a two detail columns with one having every date of the next two years. The other is where I would like the Amount to show up next to it, as specified in the above table. I've tried a vlookup but it includes the amount specified above on the date referenced AND every date thereafter. I would like the Amount to show up only on the dates referenced above. Does that make sense?

Thank you in advance!


I have a series of monthly cash flows, less than 1 year in total duration, that I want to calculate the annual IRR for. Using XIRR seems to give me a pretty substantial IRR. I'm questioning whether it's valid to use XIRR when the cash flows are < 1 year. Possibly, XIRR has more utility with yearly cash flows, or at least future cash flows spanning multiple years? As an example, the cash flows below would be an example of what i might have. I know the actual date of the cash flow will impact the IRR, but i'm putting in a first of month date for ease. Is XIRR still the appropriate method to calculate the annual IRR? I'm fairly certain calculating the periodic IRR and multiplying by 12 is inappropriate, but maybe you can tell me.

5/1/2010 - -947
6/1/2010 - +325
7/1/2010 - +325
8/1/2010 - +325
9/1/2010 - +325