Table Entries Using Dates in the Formula


Every now and again, I analyse the corporate bond data of and Apple Inc. I go and get the data and then carry out my analysis. One of the analyses I do is to create a table of all interest payments and all redemptions of principal using dates. Remembering that some of these bonds have a maturity of up to 40 years, the output table can be quite large.

The Problem

Take a look at the following screenshot to see how the bond data first appear:

Overview of the corporate bond data of

amzn Corporate Bond Data





What we see here is the face or par value of the bond, in the Offering column. We can see the Coupon and the yield rates and we can see the maturity data.

Keeping it as simple as possible by creating just annual columns not half yearly columns, I wanted to create a table like the following:

amzn corporate bond interest payments and redemption schedule

amzn Interest and Redemption payments

Of course, I can do all of this by hand. However, even though there are relatively few bonds in issue with amazon, Apple Inc has 53 bonds in issue. The point here is that doing this manually will take too long because the bonds have different maturities. Some bonds have one or two payments in the year of redemption and in the current year. We need to create a formula for interest payments and for interest plus redemption payments.

All in all, that’s too complicated and would take too long.

Formula Needed

Because of the above, we should create a formula to help us to create the interest and redemption payments table.

Before you read on, why not download my Excel file that contains the data and try to create the formula yourself? The file contains the solution so you can check your work but here is the thought process that will help you to plan your formula. The data are on the amzn_debt_data and apple_debt_data tabs.

Planning the Formula

I created my formula using the following logic:

  • Is the current column in the redemption year of the bond?
  • If so, do we make one or two interest payments?
    • that is, is the bond operative before June in the current year and/or before June in the maturity year?
  • If so, include the principal amount for redemption
  • If the column is for a year before the final year column
  • do we make one or two interest payments?
    • that is, is the bond operative before June in the current year and/or before June in the maturity year
  • Otherwise, enter nothing

My formula includes the use of these functions

  • IF()
  • AND()
  • YEAR()
  • MONTH()
  • as well as some arithmetical calculations

The Formula

Here is my formula from cell P6 of my amzn_payments tab


You now drag that formula down the column until the row of the last bond and drag it right, to the column headed 2057, in the case of amzn

Look again at the second table, above, to see that the formula automatically creates entries for the year’s interest payments and it also automatically creates an entry for the redemption of the principal, par value, amount. You can see that the bonds in rows 8, 9, 15, 16, 17 all mature between 2019 and 2023

Bonus: Yield Curves

As part of this exercise, I also created the Yield Curves for amazon and Apple and here is the yield curve graph:

amzn yield curve graph

amzn Yield Curve Graph

I am sure you can see how this yield curve fits the theory of the yield curve nicely! The Apple yield curve is even better looking because Apple has issued so many more bonds than amazon:

apple yield curve graph

apple Yield Curve Graph

There you are! Some excellent work with real data relating to the Corporate Bonds of and Apple Inc.


Download the Excel file here: amzn_aapl_debt_review_trainer


13th September 2019