Forum Discussion
spreadsheet to calculate interest using Applicable Federal Rates (AFR)
SlippySlope wrote: ``Is there a spreadsheet to calculate interest using Applicable Federal Rates``
To what end, and in what form?
Primarily, the (US) IRS AFR simply specifies a minimum interest rate for a family loan that is initiated in a particular month. I found this description online (click here). (The AFR is also used for other purposes that are less common.)
So calculating interest "using an AFR" is the same as calculating interest for any loan.
As you asking how to do that? Or are you asking for a spreadsheet template for an amortization schedule for any loan?
FYI, I would not rely on any amortization templates -- at least, none that I have seen. "All" of them use CUMPRINC and CUMIPMT, which calculate the (unrounded) periodic payment internally. That differs from any payment that you might choose -- although the difference might be insignificant.
Instead, I would approximate the interest paid between period numbers "start" and "end" as follows (periods are numbered from 1 to n, beginning with the period after the "pv" period):
=FV(rate, start-1, pmt, pv, fv) - FV(rate, end, pmt, pv, fv)
where "rate" is the periodic rate (e.g. monthly). In the US, the periodic rate is usually annualRate/freq, where "freq" is the number of payments per year (e.g. 12 for monthly payments).
(Caveat: I am unfamiliar with the AFR per se. It is unclear to me if the AFR documents specify subannual rates as annual rates or periodic rates.)
-----
On the other hand, if the interest rate for your family loan is less than the applicable AFR in the month that the loan was initiated, you might have to report additional income based on the difference between the actual interest and the "imputed" interest based on the AFR.
The actual interest is calculated by the formula above.
The AFR interest is calculated by the same formula, substituting the periodic AFR for "rate".
SlippySlope wrote: ``Is there a spreadsheet to calculate interest using Applicable Federal Rates``
To what end, and in what form?
Primarily, the (US) IRS AFR simply specifies a minimum interest rate for a family loan that is initiated in a particular month. I found this description online (click here). (The AFR is also used for other purposes that are less common.)
So calculating interest "using an AFR" is the same as calculating interest for any loan.
As you asking how to do that? Or are you asking for a spreadsheet template for an amortization schedule for any loan?
FYI, I would not rely on any amortization templates -- at least, none that I have seen. "All" of them use CUMPRINC and CUMIPMT, which calculate the (unrounded) periodic payment internally. That differs from any payment that you might choose -- although the difference might be insignificant.
Instead, I would approximate the interest paid between period numbers "start" and "end" as follows (periods are numbered from 1 to n, beginning with the period after the "pv" period):
=FV(rate, start-1, pmt, pv, fv) - FV(rate, end, pmt, pv, fv)
where "rate" is the periodic rate (e.g. monthly). In the US, the periodic rate is usually annualRate/freq, where "freq" is the number of payments per year (e.g. 12 for monthly payments).
(Caveat: I am unfamiliar with the AFR per se. It is unclear to me if the AFR documents specify subannual rates as annual rates or periodic rates.)
-----
On the other hand, if the interest rate for your family loan is less than the applicable AFR in the month that the loan was initiated, you might have to report additional income based on the difference between the actual interest and the "imputed" interest based on the AFR.
The actual interest is calculated by the formula above.
The AFR interest is calculated by the same formula, substituting the periodic AFR for "rate".
JoeUser2004 Thank you for your very through reply. Believe me, it was much appreciated as I'm not an accountant and while I use Excel a lot for basic spreadsheets, I don't use many of the powerful tools that it has. You asked the precise questions: 1) I am making a loan to a family member over a 30 month period, payable quarterly. 2) I found the website page where the AFR is published monthly. 3) I was hoping there was spreadsheet template where I plugin the loan amount, the payment term period, the AFR and it calculates the interest amount and payment amount.
Maybe the template doesn't exist...I did a search and didn't find one.
I really appreciate you gave such an elaborate response to my question.
SlippySlope wrote: ``I was hoping there was spreadsheet template where I plugin the loan amount, the payment term period, the AFR and it calculates the interest amount and payment amount.``
Some of your requirements are unclear to me. But perhaps the attached Excel file will meet your needs.
- @JoeUser Thank you very much for the template. I'll have a go with it.
Really appreciated.