I received the following request from a colleague, and, while I can do a fair amount in Excel, I am by no means an expert. I’d appreciate any suggestions anyone can make as to how to set up a spreadsheet for the following scenario. What formulae can I use to calculate simple interest on each amount based on the date of the theft to today’s date taking into account any repayment amounts. Does anyone know of a template or an online example that would help me with this issue? As always, I really appreciate the help from this group.
“We are doing a damages analysis regarding the theft and repayment of funds. The stealing occurred at many different times and the repayments were made in a similar, non patterned way. So, we want a way to calculate the “ongoing” interest that should be paid on the loss.
Claimant A Running Net loss Interest payable to date
Theft of $100,000 on 1/1/2000 100,000 ______
Theft of $200,000 on 3/2/2001 300,000 ______
Repayment $30,000 on 5/2/2002 270,000 ______
Repayment $25,000 on 3/4/2005 245,000 ______
We need to pay 6% simple interest on the “losses for this person” up to the date of repayment, which we will assume is today, but would
be the date we finally agree to the payment of restitution to the victim.”
