compound interest amortization schedule excel

Implement thread.An Equated Monthly Instalment or EMI is the fixed sum of money you pay each month whilst repaying your Home Loan.Data Science vs Big Data vs Data Analytics.Lrow = Sheets(2).Range('A' & Rows.Count).End(xlUp).RowĬells(lrow + 4, 2).Value = Format(intRateYrs, '#.#') & ' %'Ĭells(lrow + 4, 3).Value = Format(intRateMths, 'Percent')Ĭells(lrow + 6, 2).Value = Format(initLoan, 'Currency')Ĭells(lrow + 7, 2).Value = Format(payment, 'Currency')Ĭells(lrow + 10, 2).Value = 'Beginning Balance'Ĭells(lrow + 10, 6).Value = 'End Balance'Ĭells(lrow + 10, 7).Value = 'Total Interest'Ĭells(lrow + 10, 8).Value = 'Total Principal'Ĭells(lrow + 10, 9).Value = 'Total Repaid' 'initLoan = InputBox('Input Loan amount:') 'loanLifeYrs = InputBox('Input Loan life (Years):') 'intRateYrs = InputBox('Input Interest rate (Annual):') This is how the result looks 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 functioning VBA script (shown below) to compute the amortization schedule, but instead of manually entering the data, I want it to take the inputs from the three columns, calculate it for 7328 rows, and add the numbers 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.

Compound interest amortization schedule excel