
Easytofollow video tutorials help you learn software, creative, and business skills.Become a member
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 TBill. 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 30day months and that means it is a 360day 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.