Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
Numbers and financial data drives today's business world and Excel 2007: Financial Analysis can help decode this information. The proper understanding of these numbers, and the formulas behind them, can be the gateway to corporate and personal success. Microsoft MVP (Most Valuable Professional) Curt Frye teaches basic fluency in corporate finance, enabling users to see the meaning behind essential financial calculations. Curt explains how to review formulas to ensure they have the proper inputs, and shows how to interpret formula output. He also covers how to calculate leverage ratios and amortization and depreciation schedules, as well as forecast future growth. Exercise files accompany this course.
Many businesses combine cash flows from several sources, such as asset sales or retail revenues, to fund other investments. These revenues don't always come in on a regular monthly or annual schedule, so you can't use the NPV or IRR functions to calculate net present value and internal rate of return. Excel does have two functions, XNPV and XIRR, that enable you to calculate net present value and internal rate of return for cash flows that occur at irregular intervals. The XNPV function has three arguments, the discount rate, the cash flows, and the dates the cash flows occur.
XIRR also has three arguments: the cash flows and the dates of those cash flows, both of which are required, and if you want, a guess at the internal rate of return. Both the XNPV discount rate and your guess, if any, at the internal rate of return, should be expressed as annual percentage rates. So let's take a look at the two examples I have here. Actually, it's the same example twice. We are just going to calculate XNPV for one and XIRR for the other. So to calculate XNPV, type =XNPV, put in the rate, which is in cell B5, which we divide by 12 because we assume interest will be compounded monthly.
Now we enter the values for the cash flows. Those run from B8 to B16. Now we can enter the dates, those go from A8 to A16, close the parenthesis, and we have an XNPV or net present value of $24,158.80. Now let's take a look at the same investment but this time in terms of its internal rate of return. Type =XIRR. We have the values from cell E8 to E16, the dates from cell D8 to D16.
We'll leave out a guess. I think Excel's default starting value will be fine. Close the parenthesis. Hit Return. This investment has an internal rate of return of 10.77%. You are very unlikely to find any sort of a guaranteed investment with that high of a rate. So it looks like this investment is a good deal, assuming the risks are in line with what your investment strategy holds. Whenever you need to evaluate investments, where cash flows occur at irregular intervals, use the XNPV and XIRR functions.
There are currently no FAQs about Excel 2007: Financial Analysis.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.