How to Convert a Discount Into an Interest Rate in Excel
The discount, or discount rate, is the relative value of an investment security at the present time based on the length of time until it matures and its final value. Create an equation in Excel that will return the interest rate on the security if you know the length of the investment, the current cost and the final value. The simple idea is that a security that's worth $200 a year from now at a five percent interest rate would only be worth $190 today, which is a five percent discount rate for the money. The discount rate is, essentially, subtracting the interest rate from the final value of a security.
Other People Are Reading
Instructions
-
-
1
Launch Excel and create a new blank spreadsheet. Type column headings across the first row: "Settlement" in the first cell, "Maturity" in the second cell, "Price" in the third cell and "Redemption" in the fourth cell. Label the fifth column "Interest Rate."
-
2
Enter the data for your securities below the column heads. For the "Settlement" column, enter the date on which the security was purchased. In the "Maturity" column, enter the date on which the security will be mature. In the "Price" column, enter the initial purchase price -- the price at settlement -- for the security. Type the mature price in the "Redemption" column.
-
-
3
Type the following formula in cell "E2," just to the right of your first redemption price: "=DISC(A2,B2,C2,D2)" and then press "Enter." Click to highlight the cell and then click the "%" button in the "Number" section of the "Home" tab in the ribbon. The returned value is the discount rate, which is equivalent to the interest rate.
-
1
