Calculate a Forward Rate in Excel

You need to have the zero-coupon yield curve information to calculate forward rates, even in Microsoft Excel. Once the spot rates along that curve are known (or can be calculated), compute the value of the underlying investments after interest has been accrued and leave in one cell. Then link that value into a secondary forward rate formula. This produces a forward rate between two investment periods.

Compute Value of Underlying Investment

Suppose you're looking at a two-year $100 investment with a 7% annual interest rate. Its one-year interest rate is only 4%. In each case, it's easy to compute the final value in Excel.

The one-year final value for the investment should equal 100 x 1.04. This can be otherwise written as "=(100 x 1.04)" in Excel. It should produce $104.

The final two-year value involves three multiplications: the initial investment, interest rate for the first year and the interest rate for the second year. Thus, the Excel formula can be shown as "=(100 x 1.07 x 1.07)." The final value should be $114.49.

Forward Rate Formula

The forward rate is the interest rate an investor would have to be guaranteed between the first investment maturity and the second maturity to be indifferent (at least in terms of returns) between picking the shorter or longer.

In other words, you need a formula that would produce a rate that makes two consecutive one-year maturities offer the same return as the two-year maturity. You know the first one-year maturity value is $104, and the two-year is $114.49.

To do this, use the formula =(114.49 / 104) -1. This should come out to 0.10086, but you can format the cell to represent the answer as a percentage. It should then show 10.09%. This information can help you determine your investment horizon or act as an economic indicator.

Take the Next Step to Invest
The offers that appear in this table are from partnerships from which Investopedia receives compensation. This compensation may impact how and where listings appear. Investopedia does not include all offers available in the marketplace.