If you’ve managed to follow the course completely through, you will be well qualified to use Excel’s investment appraisal formulas in financial modelling without fear of error.
Excel financial modelling: course summary
To summarise the key points from this course:
- Discounting is the opposite of compounding. We discount cash flows because money today is worth more than money tomorrow (or, as the saying goes, a bird in the hand is worth two in the bush)
- Totalling the discounted cash flows (DCF) tells us a project’s value
- To get from DCF to NPV we subtract the cost of investing in the project. NPV is a key measure of project return. An analyst would be tempted to proceed with a project that would deliver positive NPV
- IRR (the internal rate of return) is another measure of project return. It is the effective interest rate that turns an investment into proceeds (=the compound annual growth rate for the project) and it’s the discount rate that makes NPV = 0
- There are three ways of calculating IRR. Using algebra for a simple cash in cash out, setting NPV = 0 using goal seek, and using the IRR formula in Excel.
And to summarise the traps when using these Excel formulas in financial modelling:
- The IRR formula has to be set up carefully. Where you have a five year project you will need to use six columns. When using the IRR formula you must enter 0 (zero) values for years where you have no cash flow
- The IRR formula only works for annual cash flows. Where you have semi-annual or other non-regular cash flows, you need to use Excel’s XIRR formula.
- The standard NPV formula will always discount the first cash flow you point it to, whether you want it to or not. To set the formula up correctly, you will need to point the formula to the cash flows you want to discount. Then you will need to subtract the cost of the investment
- Excel’s XNPV formula will not discount the first cash flow you point it to. But XNPV will always apply zero discount to the first cash flow in a series, no matter how far into the future that cash flow is expected to occur. If you want the XNPV formula to discount to today’s (or a particular valuation) date, you will want to include an initial zero cash flow attached to the valuation date.
Financial modelling course quiz
To sit the end of module test, please see the online Excel financial maths course quiz.