The Macaulay duration of a zero-coupon bond is equal to the time to maturity of the bond. What is a zero-coupon bond? Simply put, it is a type of fixed-income security that does not pay interest on the principal amount. To compensate for the lack of coupon payment, a zero-coupon bond typically trades at a discount, enabling traders and investors to profit at its maturity date, when the bond is redeemed at its face value.

The Macaulay duration is calculated by adding up the coupon payment per period, multiplied by the time to maturity, divided by 1, plus the yield per period raised to the time to maturity. The resulting value is then added to the total number of periods, multiplied by the bond's par value, divided by 1, plus the yield per period raised to the total number of periods. The resulting value is divided by the current bond price.

For example, assume you hold a two-year zero-coupon bond with a par value of $10,000 and a yield of 5%, and you want to calculate the duration in Excel. In column A and B, right-click on the columns, select "Column Width", and change the value to 30 for both columns. Next, enter "Par Value" into cell A2, "Yield" into cell A3, "Coupon Rate" into cell A4, "Time to Maturity" into cell A5, and "Macaulay Duration" into cell A6.

Enter "=10000" in cell B2, "=0.05" into cell B3, "=0" into cell B4, and "=2" into cell B5. In cell B6, enter the formula "=(B4 + (B5*B2)/(1+B3)^1) / ((B4 + B2)/(1+B3)^1)." Since a zero-coupon bond only has one cash flow and does not pay any coupons, the resulting Macaulay duration is 2.