Mortgage interest rates

[sreidpe]

Hi,
I'm building a spreadsheet to monitor the status of my mortgag
repayments.
Each monthly payment is part interest and part repayment so that eac
month the outstanding amount reduces and therefore the interest an
repayment parts change each month.
The calculation refers to a fixed cell called interest_rate. If th
interest rate changes, how do I stop the cells showing the payment
already made recalculating at the new interest rate?
Thanks
Sand

 

[Don Guillett]

put it in the formula or in a separate column so you will know that it is
reflecting the change.

 

[joeu2004]

I'm building a spreadsheet to monitor the status of my mortgage
repayments. [....] The calculation refers to a fixed cell called
interest_rate. If the interest rate changes, how do I stop the cells
showing the payments already made recalculating at the new
interest rate?

You cannot, not with a single "fixed cell called interest_rate". I
would do one of two things:

1. Have a table of applicable interest rates, and refer to the
appropriate table entry in each formula that relies on the applicable
interest rate.

2. Have a column for interest rate, just as I presume you have a column
for payment amount, and the formulas that rely on interest rate refer
to the cell in their respective row. In other words, you replicate the
applicable interest rate down the column.

There are fancy ways of accomplishing #1 so that you do not have to
"hardcode" a cell reference; for example, lookups based on date. But I
think that is "killing an ant with a sledgehammer". KISS.