VBA Express : Excel - PMT function but periodic payments increases yearly or per cut-off

Excel

PMT function but periodic payments increases yearly or per cut-off

Ease of Use

Easy

Version tested with

2000

Submitted by:

chitosunday

Description:

This will compute the initial periodic payment which increases per year or per cut-off

Discussion:

There is increasing use of this function because of the significance of inflation rate or other factors which now tend give a more realistic periodic payment .

Code:

instructions for use

			Function pmtinc(nper As Integer, morate As Single, cpv As Double, rateinc As Single, Optional chgnper = 12) As Double 
     'nper = Total no. of Periods
     'morate= period rate
     'cpv = principal
     'rateinc=rate of increase
     'chgnper = period cut off for rate increase
    Dim pctr As Integer, i As Integer 
    Dim rsulta As Double, rsultb As Double 
    Dim emi As Double, emib As Double, emic As Double 
     'emi is your current estimated periodic payment which change per cut-off
    emi = Pmt(morate, nper, cpv) 
    emi = emi * ((1 + rateinc) ^ -(nper / chgnper)) 
    Do 
         'pctr= counter to stop if it loops too long
        pctr = pctr + 1 
         'resultb is the second to last result of of the future value of your principal and payment
        rsultb = rsulta 
         'emic is the second to last estimated initial periodic payment
        emic = emib 
         'emib is the current estimated initial periodic payment
        emib = emi 
         'rsulta is the current future value of your principal and payment
        rsulta = Abs(cpv) 
         'this will compute the future value per cut-off until the last cut-off
        For i = 1 To (nper / chgnper) 
            rsulta = -FV(morate, chgnper, emi, rsulta) 
            emi = emi * (1 + rateinc) 
        Next i 
         'accept the results to the nearest two decimals
        If WorksheetFunction.Round(rsulta, 2) = 0 Then Exit Do 
         'this just estimate which way to go first
        If pctr = 1 Then 
            If rsulta > 0 Then 
                emi = emib * 1.1 
            Else 
                emi = emib * 0.9 
            End If 
        Else 
            If rsultb - rsulta = 0 Then Exit Do 
             'this try to make closer computation to target
            emi = emib - ((rsulta / (rsultb - rsulta)) * (emic - emib)) 
        End If 
    Loop Until pctr > 500 
    pmtinc = emib 
End Function

How to use:

Copy the code above
then go to your excel current opened file
Press "alt f11", you must see a directory tree if you do not see, press "ctrl R"
In the directory tree right click , insert module
in the Module ,Put this in the module code .
Close the Module.
In your excel sheet , you can put the formula like the example below
=pmtinc(C10,0.01,$C$9,C12,C13)
If you do not input the last criteria "c13" in my example which is the cut-off period, it assumes to be 12

Test the code:

The amortization table for the balance at the end should be zero

Sample File:

PMTINC.zip 18.54KB

Approved by mdmackillop

This entry has been viewed 85 times.