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.
One of the more conservative investment strategies available is to purchase an instrument such as a certificate of deposit or fixed rate annuity that enables investors to trade lower risk for relatively low but known rates of return. You can evaluate this type of investment using the future value or FV function. The FV function takes five arguments, rate, number of periods, payment, present value and type. The rate is the annual percentage rate divided by the number of periods in a year.
Usually, that's 12 representing 12 months. NPER is the number of periods in the investment. That's usually the number of years times the number of periods per year. So a ten-year investment would have 120 periods. PMT or Payment is the amount of money paid into the investment each period if any. PV is the starting value of the investment and type indicates whether the payment is due at the beginning of a period, in which case the value is 0 or omitted or at the end of the period, in which case the value is 1.
If you leave out the Payment argument, you must include a PV or Present Value to indicate the starting principal balance. So let's take a look at the formula that I have in my worksheet. I have a rate of 6%, a term of 5 years and also in this case, we have a payment of $10,000 a month and a present value of $100,000. So what that indicates is that we have an initial starting balance of $100,000 in this investment and then we'll be adding monthly payments of $10,000 to the investment.
Now, any money you pay into the investment is expressed as a negative number because it's a cash flow from your account. Therefore, it decreases your net worth by that amount so it's expressed as a negative number. In this case, the payment in PV arguments will have negative values. If you were to take money out each month, the PMT value would be positive. One such case would be if you created a retirement account and started taking disbursement after age 55. In that case, you would input the present value as a negative number and express the PMT or payment as a positive number to indicate how much money you drew from the account each month.
So let's see what the future value of this investment would be. We start with the FV function and we begin entering our arguments. So we have a rate in cell B3 of 6% but in this example, interest is compounded monthly. So we divide by 12 the number of months in a year, then we enter the number of periods. That is based on the value in cell B4, the term of the loan or the investment. But because we are using the number of periods as oppose to the number of years, we multiply 5, the value in B4, by 12.
So we get the number of months the number of times that interest is compounded. Then we have the monthly payment, which is in cell B5. The present value which is in cell B6 and because we are going with the default type where payments were due at the beginning of the period, as oppose to the end, we can just leave the Type argument blank and there you have it. If you start a loan or an investment with the present value of $100,000 at $10,000 per month, at an annual rate of 6%, at the end of five years, you will have over $832,000.
The FV function offers investors a straightforward means of evaluating the fixed rate investment. What's more? It gives analyst the ability to evaluate annuities for the beneficiary he receives periodic payments.
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.