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 first rules you learn in business is that no one gives you money for free. If you plan to borrow money with the goal of making a profit, you should expect to pay interest so that the institution that loaned you money can make a profit too. There are two main ways to calculate interest, Simple Interest and Compound Interest. Simple Interest is calculated based on the principal, interest rate, and time only. In other words if you pay a loan back over time, the unpaid interest doesn't part of the principal. As an example, take a look at the loan terms we have in this worksheet.
We have a principal of $200,000, a rate of 6%, and a term of 5 years. So to calculate the total payment on the loan we'll add the principal to the principal times the rate times the term. And we have $260,000. You can verify that answer is correct. 6% of $200,000 is $12,000. So you multiply that by 5, which would be $60,000, and you have a total of $260,000.
One other scenario that uses simple interest would be in annuity that guarantees a set return each year. Most loans and investments calculate their vales using compound interest. But you might counter simple interest investments, so you should know how to calculate it. Unlike simple interest, investments that use compound interest include previously earned interest when determining how much interest to pay the next time a payment is due. One example would be a certificate of deposit with an annual percentage rate of 6%. So we have the example here in the worksheet and you'll notice that the terms of the loan are exactly the same as we had for the simple interest example.
We have a principal of $200,000, a rate of 6%, and term of 5 years. To calculate the total due over the life of the loan or the life of the investment, you use the formula here in cell D7. You multiply D3, which is the principal, by 1+ the rate, which is 6%. So, that would be 1.06, and you raise 1.06 to the power of 5, which is the number years. So, to calculate the future value of the loan, you type in the formula D3, again the principal, times 1+B4, the rate, raised to the power of the value in B5, the number of years, and press Return.
There you have the future value of the loan. Now, please note that the future value of the investment is $267,645.12, which is greater than the $260,000 result from the simple interest calculation. That is the power compounding interest monthly as opposed to annual and for including previously earned interest in future interest calculations. Now, you might also be called on to evaluate a loan or an investment where interest is compounded continuously and for that you use the formula in cell D9.
So you multiply the principal in cell B3 in this case, by the natural logarithm, and this is a function that you use to calculate it. The math is fairly advanced. You don't really to know it. You just need to know how to calculate continuously compounded interest. But you multiply B4, the rate, by B5, the term. There you have your answer, $269,971.76.
Calculating compound interest is one of the skills you use frequently in financial analysis.
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.