IRR, NPV and payback projects question

Need a formula that will give me the IRR, NPV and payback for projects to compare which one is the better investment.

Project A: PV factor 5% (cash flows -500 year 0; 86 year 1; 150 year 2; 200 year 3; 200 year 4)

Project B: PV factor of 8% (cash flows: -1000 year 0; 220 year 2; 300 year 3, 4)

Hi would anyone have the actual formulas for calculating the problem. I am trying to use the FX bar in Excel, but cannot figure out the NPV using Fx. Thanks.

Originally Posted by MoneyMaker

This is what you have to use to find net present value for start of period payments since the first payment occurs at time period 0 and Excel NPV function only finds net present value when the first payment occurs at time period 1

=NPV (5%, { -500, 86, 150, 200, 200 } ) * FV (5%, 1, 0, -1)

To add more, the answer you get from Excel NPV function assumes interest is compounded periodically yet in finance and banking interest is usually compounded on ever small fraction of time and this is referred to as continuous compounding of interest

That is the reason I gave you four different NPV formulas in the worksheet which give answers for all 4 different combinations for timings of cash flows and interest compounding and I will list these here again for your reference

As you can see these formulas look simple as compared to the last formula I listed above that looks dense

For end of period payment and discrete/periodic compounding of interest
=tadNPV (5%, { -500, 86, 150, 200, 200 } , 0 , 0)

For end of period payment and continuous compounding of interest
=tadNPV (5%, { -500, 86, 150, 200, 200 } , 0 , 1)

For start of period payment and discrete/periodic compounding of interest
=tadNPV (5%, { -500, 86, 150, 200, 200 } , 1 , 0)

For start of period payment and continuous compounding of interest
=tadNPV (5%, { -500, 86, 150, 200, 200 } , 1 , 1)


And Excel IRR function only finds rate of return for periodic compounding of interest whereas I gave you two formulas that find rates when interest is compounded either discretely or continuously


Finally Excel does not have a payback period function and I gave you two such functions, one to find payback period and second to find discounted payback period

All these function are made possible with TADXL add-in

Thanks again, I think I figured it out and this is the last of my homework. Thanks again all.

this was a good solution but your sheet had an extra addin to function which may not be very helpful when trying to learn excel basics