How to do IRRs & NPVs calcualtions without the long cashflows?

Hi,

I need to calculate the IRR and NPV of many cashflows, and the cashflows are out to 50 + years.

The cashflows are simple and each year (apart from year 0) is the same.

Is there a simple option to perform the IRR and NPV calculation with having these big cashflows cluttering up my datasheet? I.e. Just use the upfront cost, the yearly marginal benefit and the number of years?

Many thanks in advance.

Dave

Hello Dave,

It is possible in Excel to perform your calculations using financial functions other than IRR and NPV

Would you like to share your data for NPV such as the initial outlay, the remaining periodic benefit amount and the discount rate so I may present you the solution

Here are the IRR and NPV calculations for uniform series of cash flow. Note the negative sign with PV function this is so as the PV function returns a value that has a sign opposite to that of periodic payment.

discount rate = 5%
CF0 = -1,000,000
CF1 thru CF50 = 30,000

= -1000000 + PV( 5%, 50, -30000 )
($452,322.24)

NPV is negative using a cost of capital (discount rate) of 5%

=RATE( 50, 30000, -1000000 )
1.723%

The IRR on this investment is well below the cost of capital of 5%. Thus you will incur a loss

But this is quite trivial of IRR and NPV calculations involving uniform cash flows. As an example see the following scenario

There is one or more initial cost outlays (negative amount) although not necessary followed periodic annuities payments that each make series of payments. The payments terminate in a never ending payment in a particular amount. But there may also be other payments after the terminal payments. There is a schedule of discount rates for each annuity. These payments may begin at start or at end of the particular period. The compounding of interest may not be unique for each period and periods themselves are of different lengths. The discounting convention is not confined to full-year discounting and some annuities may use mid-year discounting convention.

I don't give away ideas to companies like Microsoft as I already have their blunt answer. To solve for the IRR, NPV and other financial measures for this sort of data you would have to look at 3rd party solutions such as tadXL v3.0 a collect of 104 financial functions.

Finding IRR and NPV for following sort of data with tadXL v3.0 is a breeze

Rate	5%	4%	6%	3%	2%	10%	110%	550%
Cash flows	-100	50	20	-75	50	100	1200	5000
Frequency	4	10	100	25	5	INF	40	INF
Type	1	1	0	1	0	1	1	0
Compounding	1/2	1/12	1/2	1/52	1/26	1/2	1/26	1
Period	1/4	1/4	1/4	1/4	1/4	1/4	1/12	1
Distribution	1	1	0.5	0.5	1	1	1	0.5
Hair cut	0%	10%	5%	0%	0%	20%	75%	90%

Thanks mate - it will take me a while to digest this information however I think you have solved my problem.

Many thanks, Dave

Originally Posted by thetalldude

Thanks mate - it will take me a while to digest this information however I think you have solved my problem.

Many thanks, Dave

Hello Dave,

The NPV calculation do sound a bit daunting and they are indeed difficult and time consuming (if done manually) for the schedule of data I had shown you in my reply.

To find the net present value of such a series of annuities ( that are itself annuities ) that start at different time periods where the periods are not of same length. And the cash flows are discounted at varying rates using a schedule of discount rates. The option of perpetuity ( never-ending payments ) and then the possibility of payments after infinity makes all of this a big mess to grasp while making an attempt to explain the details to a class of business freshmen at colleges.

But then there aren't many software programs that permit such net present worth computations; in this regard Excel has a long way to go to make all this possible not unless by adapting others ideas to show as their own work.

Without showing you a lengthy computer generated output for steps required to solve for net present value of the data shown in the table, here is the results from Excel NPV function that accepts 8 different schedule of values to return a single NPV value.

=tadNPV( B1:I1, B2:I2, B3:I3, B4:I4, B5:I5, B6:I6, B7:I7, B8:I8 )
230.3068245
NPV = $230.31

Originally Posted by FinancialEngineer

=tadNPV( B1:I1, B2:I2, B3:I3, B4:I4, B5:I5, B6:I6, B7:I7, B8:I8 )
230.3068245
NPV = $230.31

Argh: data entry error

When I imported the schedule of data listed in earlier reply from the text file, it was showing the values for compounding and period in date format as in the table I omitted the = sign required to use those numbers as fractions.

While editing those cells I overlooked the data for cell E5 that was suppose to have a value of 1/52 for weekly compounding but my amendment left the cell E5 with a value of 1 as in annual compounding. Thus the results for NPV shown above were correct if the cell E5 had a value of 1 but the table I listed earlier assumed a value of 1/52 for cell E5

Now making this correction in cell E5 with a value of 1/52 for weekly compounding the correct NPV result is $240.29 for the data shown in the table above.

=tadNPV( B1:I1, B2:I2, B3:I3, B4:I4, B5:I5, B6:I6, B7:I7, B8:I8 )
240.2856850
NPV = 240.29

It seems like a good function, and it may of been of assistance, however my company will not pay for additional excel functions unfortunately.

Thanks for your help,

Dave

Originally Posted by thetalldude

It seems like a good function, and it may of been of assistance, however my company will not pay for additional excel functions unfortunately.

Thanks for your help,

Dave

I wasn't asking for money,

I was attempting a demonstration of the functions that are part of http://tadxl.com