I no longer have this file, I can build something similar upon request. The original was lost in a transfer of computers and I have not had the demand to build a new one.
So let me walk through what this was specifically for. It is for calculating a money judgment and in the same sense calculating workers compensation interest accrual.
The way the law reads is that you have to pay interest on a debt based on the 5-Year U.S. treasury notes. This gets re-calculated every 6 months as the rate on these notes is announced. This presented an interesting problem. If you put in a given start date and end date, that could be any day, how do you calculate the interest that has accrued on a given amount of money over 1+ years and still capture the right interest rate for the period?
Well, that is what the excel logic in this template will do. It looks up the rate of U.S. treasury notes on a day by day basis based on each day in-between the date (that will all auto-calculate in the model).
That rate is then applied to the given balance at the time and divided by 365 days subsequently giving you the daily interest accrual.
The model will then allow you to input a given compounding period 365 days + and every time that amount of days passes, the interest will be added to the balance and the new daily interest will go off of that new balance.
This logic will then allow the user to enter a given date range within any 20-year period and a balance that interest is to be based on, and the interest will auto-calculate given all those variables and changing treasury rates over time.
You will just need to make sure every 6 months you add the new U.S. treasury notes (5-year).
Also, note that this is based on money judgment logic, which adds 1% to the treasury rate when calculating the amount of interest accruing. I made this calculate by day so there was no discrepancy on actual accruals.
