Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In this course, author Curt Frye shows how to perform a wide range of financial calculations quickly and easily using the many financial functions found in Excel 2010. The course details dozens of functions for evaluating cash flows; calculating depreciation; determining rates of return, bond coupon dates, and security durations; and more.
One of the more conservative investment strategies is to purchase a certificate of deposit or another fixed-rate annuity that trades lower risk for a relatively low but known rate of return. You can evaluate this type of investment using the future value or FV function. The FV function has five arguments and I've laid those out in this workbook. The first is the rate and that is the annual percentage rate. Then next is periods and that is the number of periods over which interest will be accumulated.
So in this case, I have it set as 5 years. Then the next argument is the payment and this is the payment that you make every period. So for example in this case every year you'll put in an additional $10,000. Then we have the present value and that is $100,000 and that's the amount that you start with. You can think of it as a down payment on a house for example. Now both present value and payment are negative numbers, so they're displayed in parentheses. And the reason they're negative is because it's money that is flowing out of your account that you don't have control over anymore.
And then the final argument is type and type can either be 1 or 0 and it indicates when you make your payment. Anytime that you're paying off a house or a car or any other loan where the bank collects the interest, then typically you'll pay at the end of a period. Now on the other hand if you have an investment where you capture the benefits of the interest then it's in your best interest to make your payment at the beginning of a period and to indicate that in the type argument you give it the value 1.
Okay, so now with those arguments in place, we can click in cell B9 and create the formula. So I'll type equal and then fv for future value, then a left parenthesis and the rate is in b3, comma, period is in b4, the payment is in b5, the present value is in b6, and the type is in b7. Again 0 at the end of the period, 1 at the beginning. Then type a right parenthesis to close out the formula. Everything looks good. Press Enter and when we do we see that the future value of our investment would be a little bit over $193,000.
Now let's see what would happen if we change the type to 0 in other words making our payments at the end of a period. So to do that click in cell B7, then type a 0 and remembering that the current value of our investment is 193,000 and change, press Enter and we see that the future value drops by about $3000 and the reason that happens is because we make our payment later. So if you capture the interest, it's always in your best interest to pay early.
Find answers to the most frequently asked questions about Excel 2010: Financial Functions in Depth.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
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.