Cannot figure out what's wrong with this amortization schedule formula

I inherited this prepaid insurance schedule at work, and I cannot for the life of me, figure out what is going on with the formula in column T of the "Other Ppd Exp Summary" Tab. If you know how a prepaid amortization schedule is supposed to work, the final month of an expense should decrease the prepaid balance to zero, which is happening on a lot of rows in this file, but not for a lot of other rows.

All of the yellow cells in column T are currently hardcoded, because if the formula is used, it defaults to 0, which is not correct because the value in column H, the end date, is the same as the month that is being accounted for. They are both 6/30/24, which means that there should be one last month of amortization expense.

I can't understand why this formula is working for some of the rows with a 6/30/24 end date, and not the others.

Here is what the formula is doing:

1. Dividing the total expense (column I) by the number of months between the start date and end date (Columns G and H)
2. If the reporting date (Cell A1) is greater than the end date (Column H) then the value should be zero.
3. If the Value in (Column J) is "Monthly", then the value in (Column T) should follow #1 above. If the value in (Column J) is "One Time", then the value in (Column T) should equal the full Total Expense in "Column I" .
4. If the Value in (Column K) Is set to "Expense", the formula should follow all rules above. If it is set to "Hold", the value in "Column T" should be zero.

The problem that I am finding is that the cells in (Column T) that are highlighted yellow, and are currently hardcoded, when the formula is applied, it is not taking the last month of amortization (Column I divided by the number of months between Column G and H) into consideration, and instead giving a zero value. An example of a cell where this is functioning correctly is (Cell T15), whereas (Cell T17) is not. Both values should equal one last month of amortization, but only (Cell T15) is doing that.

Is anyone able to assist?

Many thanks in advance!

Welcome to the Forum rubi1052!

It is important to provide your Excel version in your profile, especially with all the new features available in the latest version. Go to File > Account and see what it says. Note the information in the two red rectangles in my sample:

$B Excel version.jpg

Please do as you have been asked in post #2, then please explain in WORDS what the formula is meant to be doing so that we don't have to try to reverse engineer it. Thanks.

When the formula is copied from T15 to T17 and utilize the Evaluate Formula feature you'll see that at one point the formula shows -9.0949...E-19<0 which evaluates to true, which is why cell T17 displays zero.
Try changing the following formula in T17:

Formula:

Please Login or Register  to view this content.

Note that when this formula is copied up to cell T15 that cell still displays 676.00
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.