Aha success! For those who thought that the formula was unknowable , I say that's silly and I will
provide a spreadsheet example. For those who worried about the quarterly change in interest rates,
you just compute each quarter one at a time. My data is a non-leap year, but I'd change 365 to 366 for leap years, and I'm pretty confident that would be right.
I have a CP2000 form that says that on 2021 taxes there is $538 owed of interest on $8964 underreported interest and that it is due April 26, 2023. I can compute that number $538 based on nothing more than what I just wrote and the published quarterly interest rates.
Here are my computations followed by the formulas in the cells. The spreadsheet starts in A1 (upper left)
and the interest rate data is published here: https://www.irs.gov/payments/quarterly-interest-rates
One observation is that there is no 0.5% extra in this case.
RESULT IN BOTTOM RIGHT
| Year | Quarter | Int rate | Start Date | End Date | Day Count | 1.000000 | $8,964 |
| 22 | 2 | 4% | 4/16 | 6/30 | 76 | 1.008363 | |
| 22 | 3 | 5% | 7/1 | 9/30 | 92 | 1.021151 | |
| 22 | 4 | 6% | 10/1 | 12/31 | 92 | 1.036710 | |
| 23 | 1 | 7% | 1/1 | 3/31 | 90 | 1.054757 | |
| 23 | 2 | 7% | 4/1 | 4/26 | 26 | 1.060029 | $9,502 |
| | | | | | | | $538 |
FORMULAS with DATA from my example
| Year | Quarter | Int rate | Start Date | End Date | Day Count | 1.000000 | $8,964 |
| 22 | 2 | 4% | 4/16 | 6/30 | =E2-D2+1 | =G1*(1+C2/365)^F2 | |
| 22 | 3 | 5% | 7/1 | 9/30 | =E3-D3+1 | =G2*(1+C3/365)^F3 | |
| 22 | 4 | 6% | 10/1 | 12/31 | =E4-D4+1 | =G3*(1+C4/365)^F4 | |
| 23 | 1 | 7% | 1/1 | 3/31 | 90 | =G4*(1+C5/365)^F5 | |
| 23 | 2 | 7% | 4/1 | 4/26 | =E6-D6+1 | =G5*(1+C6/365)^F6 | =H1*G6 |
| | | | | | | | =H6-H1 |
