How to Calculate Imputed Interest in Excel

Businesses frequently allow customers to pay for goods and services over time without specifically stating any interest charges. However, when you accept periodic payments, the Internal Revenue Service assumes that the contract price reflects the time value of money, meaning the price includes interest charges for the payment delays. The IRS refers to these types of transactions as installment contracts and requires you to calculate the imputed interest on them using federal interest rates and present value computations.

How to Calculate Imputed Interest in Excel
How to Calculate Imputed Interest in Excel
credit: utah778/iStock/GettyImages

Navigate to the Index of Applicable Federal Rates (AFR) Rulings on the IRS website (see References). Click the ruling that corresponds to the month and year you enter into a written installment contract, or in the absence of a contract, the month you enter into the underlying transaction. Also, open the rulings for the preceding two months.

Choose the lowest semiannual AFR of the three-month period. If the payment term of the installment contract is three years or less, reference the short-term rate tables within the rulings. If more than three years, but less than nine, use the mid-term rate tables; for contracts with payment durations of nine years or more, reference the long-term rate tables.

Calculate the present value of all payments. For example, if you receive two annual $5,000 payments under the contract with the first payment due in one year at an AFR of 4 percent, input "=5000/(1.04)" into Cell A1 of your Excel spreadsheet and hit "Enter" to compute the present value for the first payment. To calculate the present value of the second payment, input "=5000/(1.04*1.04)" into Cell A2 and hit "Enter" to compute.

Calculate the total imputed principal of the payment stream. Place the cursor in Cell A3, click the "AutoSum ( ∑ )" button located on the top toolbar of Excel and hit "Enter."

Subtract the imputed principal from the total sale amount to arrive at imputed interest. Input "=10000-" into Cell A4 and click on Cell A3. Press the "Enter" key to calculate the formula.