Calculating the issue price of a bond using the NPV function in Excel

When teaching financial accounting, faculty often discuss bonds payable and how to calculate the issue price of a bond. The next time you cover this topic, consider teaching students how to calculate the issue price by using the net present value function (NPV) in Excel.

Here’s an example: Suppose a company issues a $1,000,000, six-year, 6% bond when the market rate of interest is 10%, and you want the students to calculate its issue price. Below is a screenshot of an Excel file showing how to begin setting up the facts regarding the bond.

As a best practice, create an input area (shown in blue here) where users can enter the bond facts and then use this information to calculate the bond issue price (in yellow). First, enter the bond criteria given, including its maturity date, the face value of the bond, the number of compounding periods, the stated rate of the bond, and the market rate at the time the bonds were issued.

Next, you need to determine the cash interest payments that will be paid to the bondholders each compounding period (in this case, annually). Recall that to calculate this, you multiply the stated rate of the bond by its face (or maturity) value.

In the cell for cash interest payments, input the equal sign (=) followed by the appropriate cell references (here, B9 and B7), separated by the asterisk (*) to multiply them, as shown below. By using cell references, you can change either the stated rate or the bond face value and your calculations will automatically update.

Next, create a cell for the final payment in year 6, which will include both a final interest payment ($60,000) plus the maturity value of the bond ($1,000,000). Input the equal sign in this cell, followed by the appropriate cell numbers (here, B11 and B7) separated by the plus sign (+), as shown below. Again, use cell references so that if the data changes, your final payment information will also change.

Now that your input area is finished you can use the information to calculate the issue price of the bond using the NPV function in Excel. From your NPV cell (B14) begin by clicking on the fx button to the left of the formula bar and a pop-up menu titled Insert Function will appear. In the box beside the words “Or select a category,” click the drop-down menu and select Financial. You can then scroll to find the NPV function. Click OK.

A new pop-up window titled Function Arguments will appear. Here, you will enter the criteria of the bond. The Rate is the market rate of interest, and then you must enter the cash value that will be paid each year. Since you have already calculated these amounts within your input area, you can just input the numbers of those cells (B10 for Rate and B11 for Value). (Note: The pop-up window will originally only show two Value boxes, Value1 and Value2. Pressing Tab after you input a value in a Value box will cause another Value box to appear below it. You can also scroll down to see value boxes past Value4.)

Recall from the facts that this bond will pay interest each year but that in the final year, both the interest payment and the face value of the bond will be paid to the bondholders, listed in our input area as Final Payment. Therefore, in box Value6, you will input cell number B12 rather than B11.

Once you click OK, the NPV will be calculated and displayed in the NPV cell. You will also see the formula for NPV in the formula bar.

The next time you discuss bonds payable, consider expanding your students’ Excel skills by teaching them how to perform a net present value calculation using the NPV function. It is one of many financial functions your students can learn to use, quickly and easily.

— Wendy Tietz, CPA, CGMA, Ph.D., is a professor of accounting at Kent State University in Kent, Ohio; Jennifer Cainas, CPA, DBA, is a clinical professor at the University of South Florida in Tampa; and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. See their site AccountingIsAnalytics.com for resources they have developed for teaching data analytics in introductory accounting. To comment on this article or to suggest an idea for another article, contact senior editor Courtney Vien at Courtney.Vien@aicpa-cima.com.