How to Calculate Imputed Interest in Excel

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

Warning

When comparing AFR, the IRS rulings will include a number of different rates in each table. For purposes of calculating imputed interest, you should only reference the first row denoted as “AFR.”

Tip

The present value calculation for each payment is equal to the payment amount divided by 1 plus the interest rate raised to the power of the number of periods or years until you will receive the payment. For example, if the installment contract is for four equal payments of $5,000, the Excel formula for calculating the present value of the third payment is =5000/(1.04_1.04_1.04) and =5000/(1.04_1.04_1.04*1.04) for the fourth payment.

The IRS requires that you report imputed interest as taxable income on your tax return each year. To calculate the taxable portion of imputed interest for each tax year, subtract the present value of the relevant payment from the gross payment amount.

Step 1

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.

Advertisement

Step 2

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.

Advertisement

Video of the Day

Step 3

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.

Advertisement

Step 4

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.

Advertisement

Advertisement

Step 5

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."

Step 6

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.

Advertisement

Advertisement

Video of the Day