Before we delve into the program code, we need to know some basic financial concepts. The term loan amortization means the computation of the amount of equal periodic payments necessary to provide lender with a specific interest return and repay the loan principal over a specified period.

The loan amortization process involves finding the future payments whose present value at the loan interest rate equal the amount of initial principal borrowed. Lenders use a loan amortization schedule to determine these payment amounts and the allocation of each payment to interest and principal.

The formula to calculate periodic payment is

payment=Initial Principal/PVIFA_{n}

where PVIFA_{n} is known as present value interest factor for an annuity.

The formula to compute PVIFA_{n}

PVIFA_{n}=1/i - 1/i(1+i)^{n}

where n is the number of payments. Normally you can check up a financial table for the value of PVIFA_{n} and then calculate the payments manually. You can also use a financial calculator to compute the values. However, if you already know how to write program in Excel VBA, why not create your very own financial calculator?

To calculate the payments for interest, you can multiply the initial principal with the interest rate, then use periodic payment to minus payment for interest. To calculate the balance at the end of a period, we use the formula

End-of_year principal=Beginning_of_year principal - periodic payment

We insert two command buttons into the Worksheet, one of them is for the calculation of the periodic payment and the other one is to generate the amortiztion table.

Public Sub Cmd_Calculate_Click() P = Cells(4, 3) Num = Cells(5, 3) r = Cells(6, 3) I = r / 100 PVIFA = 1 / I - 1 / (I * (1 + I) ^ Num) pmt = P / PVIFA Cells(7, 3) = Round(pmt, 2) End Sub

Dim Num, n As Integer Dim I, P, PVIFA, r, pmt, PI, PP As Double Private Sub Cmd_Generate_Click() Dim Num, n As Integer n = 0 P = Cells(4, 3) Num = Cells(5, 3) r = Cells(6, 3) I = r / 100 PVIFA = 1 / I - 1 / (I * (1 + I) ^ Num) pmt = P / PVIFA Do n = n + 1 PI = P * I PP = pmt - PI P = P - PP Cells(n + 11, 3) = n Cells(n + 11, 4) = Round(pmt, 2) Cells(n + 11, 5) = Round(PI, 2) Cells(n + 11, 6) = Round(PP, 2) Cells(n + 11, 7) = Round(P, 2) If n = Num Then Exit Do End If Loop End Sub

*Copyright ® 2008** *Dr.Liew Voon Kiong* . *All rights reserved
[Privacy Policy]

*Contact: *Facebook Page