Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
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.
When you evaluate an investment, you will know such things as settlement date, the maturity date, the amount you have to invest, and the amount you get back. What you might not know is the investment's discount rate. And the discount rate is the rate of a guaranteed investment such as a T-Bill. So in other words what is the discount rate that the creators of this investment assumed when they created it? In Excel you can use the DISC function to discover the discount rate of a security. The DISC function has the following five arguments.
the first is the Settlement date and that is the date that you buy into the investment. Next is the maturity date. That is the date that you get the proceeds from the investment. Then we have the price per $100, just the amount it takes to buy in. Redemption value per 100, that's the amount you get back at the end of the investment on the maturity date. And then the basis and a basis is how interest is calculated based on the length of months and years. In the US the standard is to use 30-day months and that means it is a 360-day year but you can also have actual where it counts the actual days in a given year.
And Europe has its own method as well. There are five different methods that you can choose from and when you're creating the formula those options pop up. But in this case we're going to use the standard North American basis and that is option 0, which is also the default if you leave that argument blank. All right! Let's go ahead and create the function. We are in cell C10, type in equal sign, and then disc. Then a left parenthesis and we can start adding the arguments. Settlement is the settlement date. That's in cell C3, type a comma.
Maturity refers to the maturity date. That's in C4, comma. PR is the price for 100. That's in C5, type a comma. The redemption value is in C6 and the basis and there is a list of items and the formula autocomplete list. But in this case we have our basis in a cell so I'll type in the address, which is C7. Then I'll type a right parenthesis to close up the functions argument list and press Tab so I don't scroll down. And I see that the assumed discount rate for this bond is 6.017%.
Bond prices are usually given as $100 price and then the redemption value is based on that $100 purchase. So I'll just switch the arguments here, so we have 100 for the price per 100, which is typical, and then redemption value per $100. So before we had about a 6% discount rate and when I changed it to 100 and 106 you see that the discount rate goes down to 5.67%. So even though there was a $6 spread between the price per 100 and redemption value per 100, changing the price from 94 to 100 and redemption value from 100 to 106 made a substantial difference in the discount rate.
The DISC function lets you find an investment's discount rate when you know the investments term, initial cost and return. You can then compare that discount rate to the return from other investments to evaluate your options.
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.