Variable Mortgage Offset Calculator Spreadsheet

I need a dynamic loan amortization spreadsheet for a variable rate loan with the addition of an offset account and the ability of additional repayments. The goal is to forecast when the loan will be paid out based on either fortnightly or monthly repayments and the offset account balance and any additional repayments made. The spreadsheet needs to compute future repayments and loan term based on new inputs without affecting previous payments and interest calculations.

The LAS spreadsheet provided in excel can be used as a reference for the layout and requirements but with the ability for various interest rates, offset account and additional repayments

Core capabilities • Variable rate mortgage

• I must be able to enter different interest rates for specific date ranges and see the schedule update for future without affecting the past calculations. Note interest is calculated daily by the lender • Offset facility
• the balance sitting in the offset account should reduce the daily interest calculation. I want the offset amount itself to be changeable period by period so I can see the impact of moving money in or out. • Extra repayments
• I need the option to enter additional, irregular payments of any size and date, with the sheet showing the shortened loan term and interest saved.

What I expect to see

1. An input area where I enter loan details (principal, term, start date, introductory or later rates, etc.).
2. A clear table or chart that displays the full amortisation schedule, beginning balance, ending balance, scheduled payment, extra payment, total payment, principal, interest and cumulative interest and the offset balance.
3. A summary section highlighting total interest paid, interest saved through the offset, and how much sooner the loan is paid off when I make extra repayments.

Excel formulas are required.

If you have suggestions for a sleeker layout or additional insights (e.g., yearly summaries, graphs), feel free to build them in.

Please let me know how you plan to structure the workbook and roughly how long you’ll need; I’m ready to move ahead as soon as we agree on scope and timeline. Budget: INR 12500–37500 Skills: Visual Basic, Data Entry, Accounting, Excel, Financial Analysis, Financial Planning, Data Visualization, Financial Modeling, Data Modeling, Data Management