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.
The net present value of an investment is the present value of the investment minus the amount of money accosted by into the investment. All of the investment's cash flows must occur at the same interval for the calculation to be accurate. In other words, if you have investments on a monthly basis, then all of them must be on a monthly basis. You calculate an investment's net present value using the NPV function. This function has two arguments: Rate, which is at the discount rate or the rate of return that you could gain from a risk-free investment, and also a range of values that contain the investment's future cash flows.
You can list up to 254 cells that contain cash flow values. As always, you should be sure to adjust the rate to account for how many times per year interest is compounded. In most cases, interest is compounded monthly, so you would divide the rate by 12. By accounting convention, if you are required to pay for the investment at the start, don't include the payment in the array of future cash flow values. Instead, you should subtract the investment amount from the total future cash flows. In keeping with standard accounting practice, you should enter the investment amount as a negative number and add each of the NPV formula's output.
Let's evaluate the two investments that I have summarized in this worksheet. To do that, we'll create an NPV formula, and we are assuming a discount rate of 4%, which is in cell B3. Because we are assuming that interest is compounded monthly, we'll divide that by 12. Now we can enter the values, the cash flows. The positive cash flows are in cells, A8 through A12. Close the parenthesis and then we add the initial investment, which is $100,000 in the cell A7.
Hit Enter and we have a net present value for this investment of $23,759.66. Now let's do the same thing for the second investment. We'll create another NPV formula. Again, we are taking our rate from B3, dividing by 12 for the number of months in a year. Our positive cash flows, cash flows to us, run from D8 to D13, and then, we add the initial investment which is -$100, 000 representing a cash flow from us.
Hit Return, and we see a net present value of $23,513.51. Because the cash flows for investment 1 generate a greater net present value, that's the investment you should go with. Now we are assuming that both investments have the same level of risk. When you use NPV to compare several investments, if all of the investments have the same level of risk, then you should go with the investments with the higher net present value.
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.