Hi Everyone,
With the way things have become over the past few years, I decided to use my time wisely and started to keep a tab on a few things, one of which is my mortgage. However, I wanted something much more in depth and struggled to find much, specially when it came to variable loan (with first 3 years were fixed).
So I decided to make a calculator/scheduler where I can record all the payments I will need to make for the life of the loan and what the interest rate would be at the time of the payment. Up the top left of the table below, I have a list of different rates, and which fortnight it went up. This way I can see the changes and effects it has on future payments etc.
The issue I am having is extra payments and offset savings (extra payments is more of the priority to figure out) which are highlighted in red.
In the mini sheet below, I want the extra payments to be deducted correctly, but without affecting the Repayment as I believe it's not meant to, but don't quote me on that.
I also have a section on the sheet called "Extra Payment Savings". Basically I want this little cell to show me how much I have saved with the repayments that have been made (a way of encouragement).
Lastly, Offset Amounts. This field in the table will be calculated based on the "Offset Account Amount" up the top left of the sheet and the current interest rate.
So far, the formulas I have used I had help with before, but that help is currently unavailable. So I am really at a loss. I appreciate any assistance!
FYI, I wasn't able to add the whole list as I am collecting data bi-weekly, but I should be able to apply what's needed to the rest....I hope! Thanks again for the help.
With the way things have become over the past few years, I decided to use my time wisely and started to keep a tab on a few things, one of which is my mortgage. However, I wanted something much more in depth and struggled to find much, specially when it came to variable loan (with first 3 years were fixed).
So I decided to make a calculator/scheduler where I can record all the payments I will need to make for the life of the loan and what the interest rate would be at the time of the payment. Up the top left of the table below, I have a list of different rates, and which fortnight it went up. This way I can see the changes and effects it has on future payments etc.
The issue I am having is extra payments and offset savings (extra payments is more of the priority to figure out) which are highlighted in red.
In the mini sheet below, I want the extra payments to be deducted correctly, but without affecting the Repayment as I believe it's not meant to, but don't quote me on that.
I also have a section on the sheet called "Extra Payment Savings". Basically I want this little cell to show me how much I have saved with the repayments that have been made (a way of encouragement).
Lastly, Offset Amounts. This field in the table will be calculated based on the "Offset Account Amount" up the top left of the sheet and the current interest rate.
So far, the formulas I have used I had help with before, but that help is currently unavailable. So I am really at a loss. I appreciate any assistance!
FYI, I wasn't able to add the whole list as I am collecting data bi-weekly, but I should be able to apply what's needed to the rest....I hope! Thanks again for the help.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A1 | A1 | =TODAY() |
| P4:P58 | P4 | =VLOOKUP(O4,$E$11:$F$20,2,TRUE) |
| O5:O58 | O5 | =O4+1 |
| Q5:Q58 | Q5 | =-PMT(P5/26,781-O5,V4) |
| S5:S58 | S5 | =V4*P5/26 |
| T5:T58 | T5 | =Q5-S5 |
| N6:N58 | N6 | =N5+2*7 |
| K5 | K5 | =AVERAGE(Q5:Q364) |
| K6 | K6 | =K5*F7*26 |
| K7 | K7 | =K6-F5 |
| K9 | K9 | =SUM(U4:U784) |
| V4 | V4 | =F5 |
| V5:V58 | V5 | =V4-T5 |
