Present Value Of A Perpetuity

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.


Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

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







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


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.


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


I am trying to figure out a formula that will return a cash flow that will result in a specific IRR target. Example:

Investor contributes $1,000,000 in year 0
Investor earns 9% compounded annually on their money (paid as cash flows allow, otherwise accrued)

Payments of cash flow are made as follows:
First to Investor to satisfy 9% preferred return Second to Investor to repay any accrued preferred return Third to repay Investor's initial contribution Fourth 70/30 (Investor/partner) until Investor receives 20% IRR Fifth 50/50 Because the cash flows don't support the 9% in the early years, the cash flows aren't the same year over year and thus I am getting hung up on using the FV calculation to back into the number required to get my Investor to a 20% return.

Is there a way to solve for a Future Value when the cash flows aren't even every year?


I'm trying to find the present value of a series of all different cash flows but I want to use a series of rates to discount these cash flows instead of one fixed rate. Is there a function in Excel that will do this? Thanks.


Hi Guys,
Iam trying to write a code that calculates individual cash flow and total year cash flow for an portfolio. Ive approached this in a "step by step" excel formula based way. But i suspect its a smarter way to do this.

Please find a screen shot of my sheet here before "processing":




what I need help/suggestions about is how to calculate Cash flow for each bond and year.

A bond pays a coupon as a percentage of face value (its 100) and a principal (its 100) when it expires. (Given my maturity.)

So bond 1, pays coupon of 7 in year 1 and 2, while in year 3 its pays coupon 7 + principal 100.

So for year 1 cell 4G, i need the formula to add only coupon as no bond has expired in year 1. The coupon is 7 for bond 1 as face value is 100, (100x0.07).
While for year 3, in cell 4I i need coupon 7 + 100 principal etc.

It supposed to look like this when calculate: (i typed them in manually):




Here's code we use to calculate bond and principal cash flow
Code:

Sub BondCashflow()

' Input parameters to determine bond cashflow

Principal = Range("B1").Value
Coupon = Range("B2").Value
Maturity = Range("B3").Value
MyYear = Range("B4").Value

' Initialise Cashflow

Cashflow = 0

' One way to compute cashflow
' If MyYear < Maturity Then Cashflow = Coupon
' If MyYear = Maturity Then Cashflow = Principal + Coupon


Greatly appreciate any info on what type of code and suggestions.


I have in the past used a standard formula to do cash flow projection for construction project. This formula commonly known as the DHSS formula. I now need a bit of refreshing as I have lost the file I created using this formula. If there is anyone of you have a template or the formula I will be glad to receive a copy.


Hi all,

I am trying to calculate the payback period for a series of cash flows. I have 5 years of cumulative cash flows from cells b24:f24 with the first year's cash flow being negative.

What is the right formula for calculating the payback period?

B24: -94,352
C24: 97,944
D24: 286,081
E24: 469,891
F24: 649,201

Thanks in advance!


Hey guys,

Hoping you can help me with something I've had trouble with. I'm trying to assign a cash flow to the correct periods in time based on years & frequency. E.g

A5= Contract Start Date
B5= Contract Length (Specifically in years)
C5= Cost (to be evenly distributed for a period of time based on start date and contract length)

D5= Frequency in years (i.e. I only want the evenly distrubuted cost to occur every 3 years over a 20 year period)

Having a bit of trouble working this out but toying with the MOD function. Any ideas/ solutions?

Much appreciated!


Hello this is my first post so thanks in advance for your help. I have a spreadsheet I am developing that requires the user to enter three variables (1) Project Start Date, (2) Project End Date, (3) Total Project Cost. I then need the Total Project cost to be distributed based on a specific 12 month cash flow profile (i.e. 5% in the first month, 7% in the second month, 3% in the third month, etc...). The % allocation by month is fixed over 12 periods, but I would like it to be applied to a project that may be longer or shorter than 12 months yet still maintain the same profile (cash flow curve shape). I have looked into Fourier Analysis, but it can't be applied in this case. Anyone know if a quick solution?


Hello,

I have read several threads on the difference between IRR and XIRR however I can not figure out why XIRR and IRR return (slightly) different results. For example: Assume I buy a 1 year, $100 bond with an 8% coupon. The payments would be as follows:

Dates Cash Flows 1/1/10 12:00 AM $ (100.00) 4/2/10 6:00 AM $ 2.00 7/2/10 12:00 PM $ 2.00 10/1/10 6:00 PM $ 2.00 1/1/11 12:00 AM $ 102.00 Return calculations IRR 2.000000000% IRR adjusted to equivalent annual rate 8.243216000% XIRR 8.243909359% XIRR adjusted to equivalent quarterly rate 2.000163342%

Notice that I adjusted the payments to fall exactly one quarter of a year apart to eliminate any possible error generated by having some quarters longer than others by a few hours.

Since IRR returns an annual rate and XIRR a quarterly rate I adjusted them so I could make an apples to apples comparison. I used these equations:

Quarterly Rate = ((1+Annual Rate)^(1/4))-1
Annual Rate = ((Quarterly Rate+1)^4)-1

I have tried to think of a way to check which one is correct and came up with this:
Dates Cash Flows IRR XIRR 1/1/10 12:00 AM $ (100.00) 4/2/10 6:00 AM $ 2.00 $ 2.1224160 $ 2.1224262 7/2/10 12:00 PM $ 2.00 $ 2.0808000 $ 2.0808067 10/1/10 6:00 PM $ 2.00 $ 2.0400000 $ 2.0400033 1/1/11 12:00 AM $ 102.00 $ 102.0000000 $ 102.0000000 Sum of cash flows $ 108.2432160 $ 108.2432361

Since the IRR and XIRR calculation concept assume that all cash payments are reinvested and earn the same return as the computed IRR, each $2.00 dollar payment should be compounded at the quarterly rate until the bond is repaid on 1/1/11. Doing this with the quarterly rate produced by the IRR and XIRR will produce the respective cash flows in the two right hand columns. Summing the cash flows in the IRR column produces $108.2432160 just as one would expect given the IRR equivalent annual rate of 8.243216%. However, XIRR produces $108.2432361 when, given its annual rate, it should produce $108.2439094, a difference of $.0006732.

Does anyone know what the issue is? Why won't XIRR reproduce the correct cash flows?

I have my number of iterations set at the max and the maximum change is .00000000001 so neither of those are the culprit.

Although the actual monetary difference in this case is small, this is a simplification of a real world financing situation I am working on and in that deal the error represents real money.

Any help would be MUCH appreciated.

-Brad


I have the initial cost of the project, and an estimate of the savings per year that the project will produce. I am using a discount rate of 3%.
Is there a function to calculate the amount of years that it will take to cover the cost of the project, by means of Net present value. i.e. discounting the savings per year with discount rate.

Inital Spend $5000
Savings per year $1000
Discount rate 3%
Simple payback is 5 years (obviously)

But if the $1000 is discounted each year, how many years does it take to cover the inital $5000.

Have to do this for about 150 different inital spends, savings and rates.

Thanks in advance.


Trying to find a template (Excel) that calculates discounted cash flow (DCF)
valuation. Also need to show calculated fair value premium over market.
Either that or a reference that tells how to do it. Any suggestions?
Thanks!



Hi,

I am building a cash flow model and am having trouble with my plug.

This is a balance sheet. Assets = Liabilities + Equity.

In Years 2-7 of the model, Cash is the plug. So I said =assets-(L+E). Since assets is the sum of all assets, it includes cash, and thus there is a circular reference.

In Year 1, I have a different plug. Goodwill is my plug in Year 1. Similar formula, circular reference.

I have iterations turned on.

However, the model won't balance appropriately. Whenever I hit save the numbers just keep getting wilder and the model doesn't balance at all.

Does anyone know why this might be happening, I apprecaite the help.


I need to calculate the present value of 360 uneven payments (30-year mortgage). Years one through five would be $1x, years six through 10 would be 1.5x, years 11 through 15 would be $1.8x, and so on.

How can I create a calculation that takes the future payments in cells A1 through A360 and discounts those back at a given discount rate?

This is very easy to do with a financial calculator, but I can't seem to figure it out in Excel. Any help is appreciated!

Hedgeman50


Simple cash flow stream:

-4,000 outflow at 12/31/07
5,000 inflow at 12/31/08
2,000 inflow at 12/31/09

discount rate of 10% yields the following:

NPV = $1,998.50
XNPV = $2,198.35

Is this because XNPV is continuously compounding? Why is it different when distance between periods is the same?

Thanks in advance,


How can I change the IRR formula that it understands that I use quarterly distributions, since if I just use quarterly numbers the excel thinks that it takes it 4 more years to get the Cash flow back and it destroys my IRR number.

Help much appreciated.

Many thanks,

eisi


Can I use the IRR formula for internal rate of return calculations for
monthly cash flows? If so do I multiply the result by twelve? If not how do I
calculate the annual IRR using monthly cash flows?

Thanks for your help.





I'm trying to create a commercial real estate rent roll with annual increases in rent to be able to use for a property cash flow. Tenants have different step-up rent dates. I don't really know even where to begin. Any suggestions?


I need to write a formula in a financial model that will automatically draw on a line of credit to bring the cash balance on my balance sheet to zero. I have done this before but can't remember how to get around the circular reference issue.

For example:


Month1 Month2 Month 3
Cash 100 -50 -75
Account Receivable 150 100 100

Total Assets 250 50 25

Liabilities

Line of Credit 50 50 50
Stockholder's equity 100 100 100
Retained Earnings 100 -100 -125

Total liabilities 250 50 25

In the above example I would to have a formula that would increase the line of credit in month 2 by 50 to bring cash to zero. I get a circular reference when I write the formual that the line of credit balance in month two =if(cash

Hi,

I am looking for an excellent 5-year financial statement in excel containing income statements, balance sheets, cash flow statement and ratio analysis comparing all the years.


Please advise.
Thank you


In engineering economy problems we want to know the rate of return on an investment that returns a series of equal annual palyments for n years. This is a simpler problem than the IRR function is designed for, since it deals with varying payments which must be spelled out in an array.

I don't want to put the same annual payment in 20 cells to use IRR, and I want the number of years to be a variable, which IRR would not lend itself to. Rate should be able to do this task in a single cell, but the results I am getting are not accurate.

Basically I want to find the interest rate that will make the present value of the annual payments equal to the initial investment.


I am working on financial model. I am having problem with the IRR function. i would like to enter the IRR value and resulted in the capex value.

Example:
year 0 1 2 3 4 5
cash -25 5 5 6 7 8
with discounted rate (or guess) is 0.08

The IRR is 7%

Now my question is
year 0 1 2 3 4 5
cash X 5 5 6 7 8
with discounted rate (or guess) is 0.08

The IRR is 10%

What function or formula i can use to solve X


Any help and input is greatly appreciated. Thank you in advance.