Hi fellow Excel nerds,
Here's the background... Our Insurance bills us erratically throughout the annual policy period, but I need to expense the insurance cost evenly across 12 months. Also, there are sometimes changes to the outstanding balance due to changes in the policy or rate fluctuations. So I set out to create a spreadsheet that captures the following information (see attached workbook):
What we pay and when we pay it (column B)
The current charge (column D)
The current policy balance - this shows what we have left to pay (column E)
A calculation that looks at what the current balance is and how many more months in the policy to get the amount I need to expense each month (column H)
A calculation that shows the remaining balance (column I) - this is last month's balance plus what I've paid/been charged in the current period (column D) minus what I have accrued for (column H)
I thought this was working for the first several months, but then I discovered that it will never bring the balance in column I to zero. You can play with this and see what I mean - if you enter the remaining balance amount in D15 and put a zero in E15, column H stops dividing the remaining balance and my account balance (column I) says inflated instead of coming down to 0 by row 19.
It seems to me that my issue lies in column H, since it stops calculating when column E goes to zero. I've had a few ideas, but nothing that works.
Any suggestions?
Here's the background... Our Insurance bills us erratically throughout the annual policy period, but I need to expense the insurance cost evenly across 12 months. Also, there are sometimes changes to the outstanding balance due to changes in the policy or rate fluctuations. So I set out to create a spreadsheet that captures the following information (see attached workbook):
What we pay and when we pay it (column B)
The current charge (column D)
The current policy balance - this shows what we have left to pay (column E)
A calculation that looks at what the current balance is and how many more months in the policy to get the amount I need to expense each month (column H)
A calculation that shows the remaining balance (column I) - this is last month's balance plus what I've paid/been charged in the current period (column D) minus what I have accrued for (column H)
I thought this was working for the first several months, but then I discovered that it will never bring the balance in column I to zero. You can play with this and see what I mean - if you enter the remaining balance amount in D15 and put a zero in E15, column H stops dividing the remaining balance and my account balance (column I) says inflated instead of coming down to 0 by row 19.
It seems to me that my issue lies in column H, since it stops calculating when column E goes to zero. I've had a few ideas, but nothing that works.
Any suggestions?