Hi everyone, I am new to the forum. I am a reverse mortgage guy and I use excel to run scenarios comparing retirement plans with and without this tool. This is what am trying to do.

From an Excel loan amortization schedule, enter information for an existing mortgage the beginning date of loan, term period and interest rate. Then from that schedule, import into another sheet the future annualized payments and loan balance. For example, for a loan taken out 5 years ago, import the monthly payment and current balance onto the sheet. Then, beginning with the current year, import into a table the subsequent Annualized Payments and anniversary Loan Balances for the next 20 years. If the loan ends less than 20 years from now, the last year may reflect the total payments for that year, i.e. 8 months of payments, $12,240

I do not want to copy and paste these numbers because they may change depending on the scenario I run.

Loan Date 01/01/2015
Monthly Payment $1,530
Current Balance $ 137,595

Year Annualize Pmts Loan Balance
2020 $ 18,360 $ 137,595
2021 $ 18,360 $ 123,948
2022 $ 18,360 $ 109,674

How can I do this? Help on this would be greatly appreciated

Thanks to everyone willing to help.