To calculate a bond's yield to maturity in Microsoft Excel, you need to have the correct numeric inputs and enter them into the RATE function in a specific order. Arranged in the right way, these inputs can be adjusted individually later in order to change the yield to maturity automatically. This can be a useful tool for comparing bonds that have different coupon rates and years until maturity.
In the first column (A), create rows with each of the following titles in cells A2 through A6 in this order: Face Value (A2), Annual Coupon Rate (A3), Years to Maturity (A4), Payment Frequency (A5) and Value of Bond (A6). These are the variables that would factor into any yield to maturity calculation .
In the second column, directly across from the proper row title, enter in the correct numeric value next to each of these variables. These should occupy cells
B2 through B6. You can change the formatting in cell B3 to show percentages and change the formatting in cells B2 and B6 to show dollars. The payment frequency is accounting for the number of payments per year. If the bond has semi-annual payments, you enter "2" into cell B5.
After inputting, find an empty cell and enter the following formula:
Do not actually enter any spaces inside of the formula in Excel. Change the formatting inside the cell to show percentages to display the yield properly.
This formula does not work as constituted with a zero coupon bond (spot rate). Instead, solve for the standard rate of return for zero coupon bonds – it's one of the basic investing Excel formulas which should be equal to its yield to maturity. This can be done because zero coupon bonds have no interest payments to reinvest.