Dominating Excel Functions for Financial Analysis is a key skill you must have to succeed in finance. Here you can check the 5 must-know Excel Functions for Financial Analysis which will change the game for you!
Excel Functions for Financial Analysis – Future Value:
Future Value Concept:
Imagine you investing £ 10,000 into a fixed-income investment which returns you 10% p.a. What will be the value of this investment one year from now? That’s exactly what the Future Value function calculates for you. The financial concept behind is straight forward as we basically need to compound the interest effect into your investment at the given time.
As per example below, you could simply multiply the value £ 10k by the “interest factor” which is “1 + 10% Interest Rate” and take that factor to the power of 1 as there’s only 1 year in this example. This generates a future value of £ 11,000 (i.e. 10,000 x (1+10%)1). Should you have the same investment applied for 2 years at 15% p.a., then the future value would be £ 13,225 (i.e. 10,000 x (1+15%)2 = £ 13,225).
Do you want to dominate Excel functions for Financial Analysis? Join now our course KEF 50 and speed up your career!
Function Future Value:
However, Excel contains the function Future Value (FV) which does all those calculations for you. You just need to be aware of its arguments:
- Rate: This is the compounding interest rate of your investment.
- Nper: This is the Number of Periods (1 or 2 years for example)
- Pmt: This is any “intermediary payment” you make or receive during the given period. It’s assumed to be zero in this specific example.
- Pv: This is the Present Value of your investment or simply the amount you invested initially.
- Type: This can be either 0 for amortization taking place at the end of a period or 1 for beginning of period.
NOTE: Excel automatically chooses the signs of the result of financial functions by following the flow of money in an investment. If you are investing your money, the Future Value must be positive as this is a cash inflow for you. It therefore assumes that the Present Value (cash outflow) needs to be negative. In other words, in case you use a positive value for the argument Present Value (“Pv”), then the result of the function FV will be a negative number. To adjust that, simply use a negative sign at the beginning of that function in case you are only interested in the absolute figure.
Excel Functions for Financial Analysis – Present Value:
Present Value Concept:
Let’s use the same numerical example above. If you understand the concept of Future Value and the function we used, you can quickly understand Present Value as well as this basically works the other way around. In this case, the question could be “what’s the today’s value of an investment worth £ 11,000 in one year from now if the compounding interest rate is 10% p.a.? In this case, we need to divide the Future Value by the Interest Factor (1+ 10%) to the power of 1, which is the number of years in this example (i.e. Present Value = 11,000 / (1+ 10%)1 = £ 10,000). Once again, we could have the same result for an investment worth £ 13,225 in 2 years from now if compounded at a 15% p.a. interest rate (PV = 10,000 = 13,225 / (1+ 15%)2).
Function Present Value:
And of course, you don’t need to remember this formula as you can basically work with the Excel function Present Value (PV). Here, don’t forget to use the negative sign at the beginning of the function to adjust the “flow of money” sign done by Excel, and let’s assume no intermediary payments and amortization taking place at the end of a period.
Excel Functions for Financial Analysis – Payment (PMT):
PMT Concept:
Now you might be wondering about an example which includes “Intermediary Payments”. The best example is your home mortgage! In a typical mortgage, you sign off a mortgage agreement which indicates a contractual interest rate (i.e. Rate), the amount of debt the bank is financing (i.e. the Present Value of the debt), which is expected to be fully repaid (i.e. Future Value = 0) at the end of the agreed contract duration (i.e. Nper). With these variables you can then calculate the payment that is taken out of your bank account every month which is represented by the diagram below.
Function PMT:
You can easily do this in Excel with the PMT function as per example below. This will generate a payment of around £ 33.4 k per year. Once again, make the appropriate sign adjustment for the function (in case you are interested in the absolute value only). Also, let’s assume the amortization occurs at the end of period.
Do you want to dominate Excel functions for Financial Analysis? Join now our course KEF 50 and speed up your career!
Excel Functions for Financial Analysis – Net Present Value (NPV):
NPV Concept:
The Net Present Value (NPV) of an investment is basically the sum of the amount to be invested at today’s date plus the present value of a stream of cash flows that will be generated by that investment during a specific time. Examples include stock which you can buy today (i.e. cash outflow) and which will produce a series of dividends you will receive in the future (i.e. cash inflow). Another example is the purchase and refurb of a warehouse (i.e. cash outflow) which will generate a strong rental income for the years to come (i.e. cash inflow).
Bear in mind that as we are dealing with present value we need to have a Discount Rate in place. This discount rate is basically the minimum return rate you demand in that particular investment. It’s usually a rate which you can easily get on an alternative investment of the same risk.
Function NPV:
Excel has 2 functions that perform Net Present Value assessment. The most basic one is the NPV which is used for payments taking place at regular intervals (e.g. every 30 days, every 365 days, etc.). As this is rarely the case, there’s another function called XNPV which considers the actual dates when you expect the stream of cash flows to occur. Therefore, the arguments of that function are the “Rate” (i.e. discount rate), the “Values” (i.e. stream of cash flows) and the “Dates” where that stream of cash flows is taking place, as per numerical example below:
In this case the NPV is positive, so it makes sense from a financial perspective to make this investment. In other words, as the sum of the present value of the future cash inflow is greater than the investment (i.e. cash outflow), the result is a positive number. This means that such an investment adds value to you or your business.
Excel Functions for Financial Analysis – Internal Rate of Return (IRR):
IRR Concept:
The last financial concept / function you need to understand is the Internal Rate of Return (IRR). This is widely used by investors globally. This is the rate of return that brings the Net Present Value formula we discussed above to zero. By using the same approach to other investments you can quickly compare which investment has the higher return. The higher this return is the more desirable it is from an investment perspective.
You should then compare this return against your own required return. For example, let’s assume you have an investment opportunity which gives you a guaranteed IRR of 15%. However, you already have your money invested at 18% p.a. (i.e. your required return). In this case it doesn’t make sense to switch investments as the IRR is lower than your required return. However, should the IRR of the alternative investment be 21%, then it would make sense to switch. This is because your IRR is now greater than your required rate of return.
Function IRR:
Just like NPV, there are 2 functions in Excel to make this assessment. The first and more basic one is the IRR (for regular intervals). The other one is the XIRR which assumes the actual dates when the stream of cash flows take place. The arguments of the XIRR function are:
Values: This is the stream of cash flows of an investment you might take.
Dates: These are the dates when you expect the stream of cash flows to take place.
Guess: The XIRR function performs an interactive calculation to solve for the rate of return. By default, it starts at a rate of 10%. It then and goes up and down until the NPV of the underlying cash flow becomes zero. This is an optional argument. If you omit it, Excel assumes 0.1 for this argument by default.
Using the same numerical example as the NPV, we can get an IRR of 23.7%. This is way above the required rate of return of 8.5%. Therefore, as this investment adds significant value, you should go ahead with it from a financial perspective.
Are you really serious about bringing your knowledge of Excel functions to the next level? So join now our training KEF 50 which is a course covering the 50 most used functions in Excel for Financial Analysis. See you in class!
To keep updated with our latest quick fire tutorials follow our social profile Instagram and YouTube.
