The former includes an interest-only period of payment, and the latter has a large principal payment at loan maturity.Īn amortization schedule (sometimes called an amortization table) is a table detailing each periodic payment on an amortizing loan. Examples of other loans that aren't amortized include interest-only loans and balloon loans.

Please use our Credit Card Calculator for more information or to do calculations involving credit cards, or our Credit Cards Payoff Calculator to schedule a financially feasible way to pay off multiple credit cards. They are an example of revolving debt, where the outstanding balance can be carried month-to-month, and the amount repaid each month can be varied. It is possible to see this in action on the amortization table.Ĭredit cards, on the other hand, are generally not amortized. Interest is computed on the current amount owed and thus will become progressively smaller as the principal decreases. A part of the payment covers the interest due on the loan, and the remainder of the payment goes toward reducing the principal amount owed. When a borrower takes out a mortgage, car loan, or personal loan, they usually make monthly payments to the lender these are some of the most common uses of amortization. The two are explained in more detail in the sections below. The second is used in the context of business accounting and is the act of spreading the cost of an expensive and long-lived item over many periods. The first is the systematic repayment of a loan over time. There are two general definitions of amortization. Implement thread.While the Amortization Calculator can serve as a basic tool for most, if not all, amortization calculations, there are other calculators available on this website that are more specifically geared for common amortization calculations.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.

Amortization schedule spreadsheet