4

We currently have an ARM on our house, bought in April of 2020 with a starting rate of 1.99%. It is up for re-amortization in April 2025. Assuming that the rate adjust to the highest allowed in the contract (3%) our new interest rate will be 4.99%. Current balance is about $145,000 and I'm assuming taxes/insurance stays the same. I am trying to figure out what our minimum payment towards principal is going to be at that time.

Using Excel FV function I can calculate the balance at April of 2025:

-FV(0.0199/12, 19, 430, -145000, 0) = -$141,344

Where 19 is the months until April 2025 and $430 is what we currently pay towards principal.

Then I plug that balance into another FV function to find what payment I need to make for 25 years to pay off the loan. I'm assuming this is a rough estimate of what the bank will do when they also calculate the new payment at that time. This gives me:

-FV(0.0499/12, 25*12, X, -$141,344, 0) = Y

Messing with "X" until I get "Y" close to zero, I come up with a payment of $826.

I'm aware that the bank will again re-amortize the loan after another 5 years which will change the payment again. However, am I calculating the future payment at least from April 2025 to April 2030 correctly?

1
  • 2
    Using the PMT function of Excel would be better than trial-and-error on FV
    – D Stanley
    Sep 13, 2023 at 20:25

1 Answer 1

5

I believe that you are confusing a pair of numbers.

in your first formula:

-FV(0.0199/12, 19, 430, -145000, 0) = -$141,344

You decided to input $430 because that is the amount of your current principal payment. The issue is that every month the amount of the principal payment increases, and in this formula the payment value should be the amount to cover principal and interest. The hint is that the formula is using an interest rate.

But if your numbers in the first formula were correct, then the 2nd calculation is fine.

You could also user the PMT() formula in excel to directly calculate the payments when you have 25 years remaining.

1
  • Yeah, I thought about the principal payment slowly increasing. Looking at my payment history it only goes up about 70c a month so I considered it negligible. Is there an Excel function that would take this into account if FV doesn't?
    – Nosjack
    Sep 15, 2023 at 18:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .