I think that I've lost my mind! I am trying to figure out what function/formula to use in either Lotus or Excel to compound interest daily for a specified period of time. I am using the CUMIPMT in Excel, but it doesn't seem to be calculating the figure that I am looking for (IRS interest charges that are compounded daily). Any ideas or suggestions would be great!
No, not a payment, but interest compounded daily. I have a situation where the IRS misapplied many many payments and I am trying to figure out the interest. The IRS compounds interest daily. So let's say that one owed: $3151.00 original tax +1496.73 penalties =4647.73 total used to calculate interest. The intererst is compounded daily. So Let's say that I'm trying to figure out what the interest, which is compounded daily, would be if it was figured between April 15, 1989 and July 18, 1994. The IRS has $2,402.99. Using the following formula, =CUMIPMT(0.144/365,1919,4647.73,1,1919,1)*-1; My result is $1,977.53. I tried the ISPMT: =ISPMT(0.144365,1919,1919,4647.73); The result is $0. Do you have any other suggestions? BTW, thanks for the response!
Well, rather than rely on built-in functions, I usually try a brute force method and play with the variables to see if I can duplicate the number. At the bottom of the following page is a spreadsheet I plugged the starting balance of $4647.73 and dates into: http://www.geocities.com/crofttk/index.html At 14.4% I calculated a total accumulated interest of $5264.00 between 4/15/89 and 7/18/94 ! When I used goalseek, to back calculate the interest rate for $2402.99 accumulated interest, I get 7.9235%. Does the spreadsheet help any ? Are you sure they're charging interest on the whole $4647.74 ? Are penalties also being added in periodically during the 1920 days ? Are you sure they use the 14.4% interest rate for the entire 1920 days ?
Re: Re: Compounding interest daily This is so confusing. The interest rate that is supposed to be used for taxes due on 04/15/89 is 12%. They charge a filing late penalty of 708.98 and a paying late penalty of 787.75. Both penalties are maxed out at what the IRS can charge (47.5%). In reading the paperwork accompanying these figures it would appear that it is possible that the IRS does not charge interest on paying late, only late filing (illogical?). I tried to do the scenario with just the $3151 + 708.98, but it didn't match up. The ending balance that you have (on the second scenario) of $7050.72 is exactly the ending balance that the IRS has. But only charging 7.9235%? I can't imagine them being kind, can you?.
Daily compounding closely approximates continuous compounding, which is actually much easier to figure because it's based on calculus. Yes, calculus does make difficult problems much easier. I derived the formula for continuous compounding in my high school calculus class years ago, but I've temporarily forgotten it. I do remember it involved the transcendental number "e".
Re: Re: Compounding interest daily Are you saying that you invented the formula for continuously compounding interest back in high school? Are you the one who sold the formula to the IRS?! Now wonder you're rich!
Re: Re: Compounding interest daily Yeah, I did that for myself in a later engineering economics class because the instructor didn't want to be bothered with it ! It's a good way to calculate continuous cash flow discount factors and you can re-derive a formula for any discount method and cashflow timing structure you want to once you use the integration approach. I have it preserved on a piece of paper in one of my textbooks. LOL The basis of the derivation is that the limit of (1-i/n)^-n as n approaches infinity is e^-in. Then, you integrate e^-it(dt) across the interval t1 to t2 and you get the general formula: D.F. = (e^-it1 - e^-it2) / i where D.F. is the discount factor However, I believe sirrowan is talking about DISCRETE daily interest compounding here. I don't know what the IRS does or is supposed to do. I'll look at the parameters sirrowan has given and see if I can intuit something.
Re: Re: Compounding interest daily Well, sirrowan, I took a few minutes to look this stuff up at the IRS web site and it's a real can of worms. Sorry to say it wouldn't matter how fancy a spreadsheet or formula I tried to concoct if I don't have a grasp of exactly what fees they charge on what date and what the interest rate being charged is on a given date. Apparently interest rates change quarterly. I can calculate interest but I ain't no "tax professional". Obviously, I would be biting off more than I could chew if I tried to make more sense of this. It'll take more than calculus to get a handle on it. Unless the IRS gave you an itemized statement of which charges occurred on which date and how the interest rates changed through time, I don't see how I could figure it out ! Sorry !
Re: Re: Re: Compounding interest daily Thanks for trying! I realized that it was hopeless when I went to plug in other years into the spreadsheet. For example, For A tax that becomes due 04/15/89, the tax rate to be used is 12%, with a SPECIAL interest rate of 120% of the underpayment if: (all apply). Which makes 12% become 14.40%. Each tax year that I am trying to calculate has a different tax rate, with the SPECIAL rate tacked onto it. My theory that I was trying to prove was that the IRS was applying payments to the tax year with the lowest interest rate and as a result maximizing their profits and killing the taxpayer. Those little suckers at the IRS probably don't want us little taxpayers to know how to calculate the interest so they can keep charging whatever they want (well not so much today, but back in the early 90's, they were uncontrollable!). IF ANYONE CAN LET ME KNOW WHAT FORMULA THE IRS USES TO CALCULATE INTEREST, I WILL SING YOUR PRAISES TO THE END OF THE EARTH, TILL THE END OF TIME!!!!!!!!!!!
