how to figure out amortization schedule in excel

'intRateYrs = InputBox('Input Interest rate (Annual):') This is how result look like from one iterationįor Each cell In ThisWorkbook.Sheets(1).Range('B2:B2001') Payment = Pmt(intRateMths, loanLifeMths, -initLoan)Ĭells(7, 2).Value = Format(payment, 'Currency')Ĭells(outRow + rowNum, 2).Value = Format(yearBegBal, 'Currency')Ĭells(outRow + rowNum, 3).Value = Format(payment, 'Currency')Ĭells(outRow + rowNum, 4).Value = Format(intComp, 'Currency')Ĭells(outRow + rowNum, 5).Value = Format(prinComp, 'Currency')Ĭells(outRow + rowNum, 6).Value = Format(yearEndBal, 'Currency')Ĭells(outRow + rowNum, 7).Value = Format(intTot, 'Currency')Ĭells(outRow + rowNum, 8).Value = Format(prinTot, 'Currency')Ĭells(outRow + rowNum, 9).Value = Format(fvloan, 'Currency')ĪctiveSheet.Range('A:I').EntireColumn.AutoFit InitLoan = InputBox('Input Loan amount:')Ĭells(4, 2).Value = Format(intRateYrs, '#.#') & ' %'Ĭells(4, 3).Value = Format(intRateMths, 'Percent')Ĭells(6, 2).Value = Format(initLoan, 'Currency') LoanLifeYrs = InputBox('Input Loan life (Years):') IntRateYrs = InputBox('Input Interest rate (Annual):') Sub one()ĭim intRate, loanLife, initLoan, payment As Doubleĭim yearBegBal, intComp, prinComp, yearEndBal, intTot, prinTot, fvloan As Currency I am requesting changing the script below to take the values from 3 columns. I already have a working VBA script(given below) to calculate the amortization schedule but I want it to take the inputs from the 3 columns, calculate it for 7328 rows(instead of manually putting it in) and append the values below each other.

I have three columns Sanctioned Amount, Tenure and Rate of Interest for 7328 rows. I am new to Excel VBA and looking for help in editing a macro.

How to figure out amortization schedule in excel