how to do a amortization schedule in excel

' Total payment = principal portion + interest portion

Schedule(Counter, 3) = IIf((DesiredPay - Schedule(Counter, 4)) < BeginBal, _ĭesiredPay - Schedule(Counter, 4), BeginBal) ' Amount of principal portion depends in part on the remaining balance before the payment ' is applied the principal amount can never be larger than this Schedule(Counter, 4) = BeginBal * PeriodRate ' Interest is easy to determine do it first ReDim Schedule(1 To Periods, 1 To 5) As Double ' Dimension array with 'first pass' amortization schedule ' This function will work in other VBA and VB projects. You can use Conditional Formatting ' to dynamically hide such results. If the array area has more rows than the ' function returns, the 'extra' rows will show a #N/A error. All arguments should be positive ' The function returns an array of length (1 To N, 1 To 5), where N is the number of payments ' ultimately needed to retire the loan (may be different from initial Periods argument if extra ' principal payments are made) ' In the second dimension, the values are: ' 1: Balance before payment X ' 2: Total amount of payment X ' 3: Principal amount of payment X ' 4: Interest amount of payment X ' 5: Balance after payment X ' To use this function in the Excel UI, use an array formula. ' This function returns an array with the amortization schedule for a loan with known ' beginning principal, period rate, number of periods, and optional fixed extra principal ' with each payment. Or, your business may have taken out a loan, and you are wondering (a) how long will it take to pay off the loan if I pay $X per period, and what will my payment schedule look like? The included function AmortSchedSpecialPmt can help you with that analysis.įunction AmortSchedTraditional(BeginPrincipal As Double, PeriodRate As Double, Periods As Long, _ The included function AmortSchedTraditional can help you with this analysis. You may also want to test various scenarios related to making additional payments to principal. You may want to mock up an amortization schedule to see just what you are getting yourself into. Suppose you are considering making a purchase funded by a loan, such as taking out a mortgage to buy a house. Functions are fully VB-compliant and may be used in any VBA or VB project (including, but not only, Excel)

Two functions that return an array with the amortization schedule for a loan, based on loan terms and payment style. Functions to return a loan amortization schedule in an array

How to do a amortization schedule in excel