Course summary: Excel financial maths
If you’ve managed to follow the course completely through, you will be well qualified to use Excel’s investment appraisal formulae without fear of error. If though, you’re a little bit cautious, and don’t always trust yourself, there is another option. That is to use the kind of tables you first saw in the downloadable Excel spreadsheet exercises showing you how to calculate DCF, NPV and IRR (= the discount rate that makes NPV = 0). They break out all the steps involved, including the discount factors, and are perhaps more easily auditable. Even if you are confident you will never fall into the traps, you may not want someone else picking up your analysis, making some changes and muddling the formulae to the point where they are not delivering accurate results!
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 discount rate that makes NPV = 0 and the compound annual growth rate for the project
- There are three ways of calculating IRR. Setting NPV = 0 using goal seek. Using algebra for a simple cash in cash out, and using the IRR formula in Excel.
And to summarise the traps when using these Excel formulae in financial modelling:
- The 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 from that the cost of the investment
- 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 a table that adjusts discount factors or use another formula like XIRR.
You might want to set up your investment appraisal calculations manually, as shown in the spreadsheet you have been working on. It will show someone else picking up your work what you have done. Plus it might stop you or anyone else making the mistakes above! Happy modelling!
Financial modelling course quiz
To sit the end of module test, please see the financial modelling course quiz.
Back to top