How To Calculate Yield to Maturity in Excel

Yielding or compounding interest is a huge part of the world of finance. Yield or compounding occurs whenever an investment money is made in such a way that it pays interest.  A lot of financial instruments for investment work on interest that gets compounded. Compound interest does not need to be computed through the use of pen, paper and calculator. If you think that this is a tedious process, there are other options for you. With the help of your computer and Microsoft Excel, the process of computing yield to maturity can be simply and easily completed.

Establish the givens. There are a number of details, which need to be at hand to compute for the yield to mature. These are: the starting principal amount made as investment, the annual interest rate in terms and the longevity or the time of investment. Have these handy with you as this will be keyed into excel as data to work on to help you compute for the yield to maturity.

Prepare your spreadsheet. Once data is at hand, open an excel file and prepare your spreadsheet. This means working on the format of your cells to help you get started as you key in information and work on identifying the compounded interest. Start with your column formats first. Start with column A making sure that it could fit about forty characters. Column B cells will be used for numbers. Format it as numbers allowing decimal points of two places. This will apply to cells B1-B4. Cell B5 should be formatted as currency.

Put on the labels. When formatting is done, the next step is to place labels on the cell. Cell A1 should be labeled or named as principal, cell A2 as your Interest rate, A3 as the interest periods per year, A4 as the number of times the conversion periods is to elapse and lastly, A5 is tagged as the Maturity Value.

Key in the Formula. Column A is down so you now move on to column B. Go to B5 (your currency cell) and key in the formula =B1*(1+B2/B3)^B4.

Input the givens. The next step you need to take is to key in data that you have at hand. Key in the principal amount that was initially invested in B1. B2 should contain the rate of interest and this will come out as a decimal value due to the formatting that you have done. B3 is for the annual interest in rate – this can be daily, quarterly etc. Follow the terms – 365 for daily, 4 for quarterly and 12 for annual terms. Key in the number of interest periods or the length of the investment as it applies

View the results. Once you have keyed in all the necessary data, then cell B5 will automatically provide you with the results. From here, you will already see the value upon maturity and you will be able to identify the yield at maturity.

Computation when the steps are followed is relatively simply. If you still find it complicated, then you can get help from the excel wizard which will make the process easier for you to understand. All you need to do is to access the Function Wizard and select the financial/price category. If you do not see this option, then you can go to Tools, select add-ins and enable the analysis toolpak.


Share this article!

Follow us!

Find more helpful articles: