You owe me $325.00 as of 1-June. Instead of paying me, you note that I will owe you $778.00 on 12-July and that we should just settle up then. I, being an accountant, suggest instead that we settle up on 10-Aug so as to prevent the other party from constantly being on the lookout for how to get 2000 dollars fast. Here’s one way to find that date:
Start by setting up a spreadsheet to compute time value of money on the two amounts.
I set an interest rate of 10%, but it doesn’t matter what the interest rate is. We won’t actually be paying interest to each other. And regardless of the rate, the settlement date would calculate the same because the same rate is applied to both balances. My initial setting for days is 41, the difference between the loan dates.
C9: =FV(C6/365,C7,0,-B4)-B4
C10: =FV(C6/365,C7-(C5-C4),0,-B5)-B5
This setup reveals that you will owe me $3.67 at 10% interest on 7/12/08. The question is: how long should I hold your $778 to accrue the same amount of interest. I used Goal Seek to find out.
Change the number of days until the difference between the accrued interest amounts is zero. And go.
If I pay you back in 70.7 days, then we will be square from a TVM point of view (and what other point of view is there).
There is no other point of view…
Accountants should rule the world.
That reminds me, Dick. You still owe me $124.16 for beer at the Summit. That was the night I paid your portion of the tab. I’ll need that money by 12-Jul.
You’re not getting enough sleep, man. You’re not getting enough sleep. :)
I am amazed at the number of people who do not know that Excel includes optimization tools (Goal Seek and Solver). As a Mechanical Engineer, I use them all the time to help me find the optimum design. I’ve even used Solver in conjunction with macros for even more power.
In my opinion, Solver is the second most powerful tool in the Excel arsenal, next to the almighty PivotTable.
Where do I find solver in 2007?
In Excel 2007, you can find Solver here:
Office button -> Excel Options -> Add-ins -> Manage: Excel Add-ins -> Go -> Check Solver -> OK
Solver should now be available on the Data tab of the ribbon.
Enjoy!