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.
Bonds that issue coupons or pay interests to investors at regular intervals offer bondholders the option to invest their earnings elsewhere. Some issuers decide to offer zero- coupon bonds, which only pay interest at the end of the investment period. A zero-coupon bond sale lists the price and the payment due when the bond matures, but doesn't usually list the investment's interest rate. You can calculate that rate for yourself using the RATE function. The RATE function has three required arguments and three optional arguments. The required arguments are nper, the number of periods, pmt or payment, which are any payments made each period.
And present value, which is what the investment is worth today. That is the cost to purchase the investment. The optional arguments are fv, the future value of the bond, type, whether you pay at the beginning or end of an investment period and guess, which is a guess as to the interest rate. If you leave guess blank, Excel assumes a value of 10%, which is often a good enough starting point for Excel to zero in on the bonds annual percentage rate. So I'll finish filling out this RATE formula. So we have the number of periods, and that is the number of years in B3, multiplied by the Periods Per Year 12, which are in cell B4, then the Payment that in this case is 0. That's in cell B5.
And then we have the Present Value. The Present Value is the amount that you pay to invest in the zero-coupon bond. Payment is 0 because you do not make a monthly payment, instead you put all the money in upfront and that indicates the present value. Present Value is expressed as a negative number, because it represents an expense or negative cash flow to you. So you have it as a negative number. And that is in cell B6, and then you have the Future Value, which is in cell B7. And in this case, you have a Future Value of $12,500.
I'm not going to use either type or guess. So I'll close the parenthesis. Now you'll notice that the formula I have here below includes a multiplication by the number of periods per year. Not multiplying by the number of periods per year results in a formula that makes sense, but actually produces a result other than what you want. So I'll press Return and you see that you have a result of 0.37%. What that means is that is that you have calculated the monthly rate. When you multiplied by 12, the number of periods per year you change the calculation so that you actually got the rate for a month.
So each month you make .37% of interest, but if I multiply the result of the RATE formula by B4, the number of periods per year then you get 4.47%. By way of checking my math, I input 4.47% into a future value formula divided by 12 and then use the same terms that I used in the RATE calculation to come up with a value of $12,499.27 which is just a rounding error away from the rate as expressed in cell B9.
You should consult with your tax professional about the advisability of investing in a zero-coupon bond. The reason is that you might owe taxes on interest earned even if you haven't received any interest 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.