VBA Express : Excel - Rate - User Defined function

Excel

Rate - User Defined function

Ease of Use

Easy

Version tested with

97, 2000, 2002

Submitted by:

chitosunday

Description:

The rateudf is basically the same in syntax as Excel rate built-in function except that it is more powerful in terms of getting results eliminating the annoying #num! error . Actually, it does not need to have a guess rate as it can cover much wider scope but was done for similarity purposes only.

Discussion:

=rateudf(period,payment,present value, future value-optional,type-optional,guess rate-optional) As you can see, the function is basically the same as Excel built-in function except that for Excel 97 , you need to input all optional data if you wish to put one optional data. Otherwise, don't put any optional data at all.

Code:

instructions for use

			Function rateudf(nper As Integer, pymt As Double, pv As Double, Optional fv As Double, Optional typ As Byte, Optional guess As Single) As Single 
    Dim ctr As Integer 
    Dim emia As Double, emib As Double, guessb As Double 
    If guess = 0 Then guess = 0.1 
    Do 
        emia = pmt(guess, nper, pv, fv, typ) 
        If WorksheetFunction.Round(emia, 4) = WorksheetFunction.Round(pymt, 4) Then 
            rateudf = guess 
            Exit Do 
        Else 
            guessb = guess * (pymt - emia) / emia + guess 
            emib = pmt(guessb, nper, pv, fv, typ) 
            If WorksheetFunction.Round(emib, 4) = WorksheetFunction.Round(pymt, 4) Then 
                rateudf = guessb 
                Exit Do 
            End If 
        End If 
        guess = (pymt - emia) / (emib - emia) * (guessb - guess) + guess 
        ctr = ctr + 1 
    Loop Until ctr >= 1000 
End Function

How to use:

Open Excel file.
Alt + F11 to open the VBE.
Insert | Module.
Paste the code in the Code Window that opens up.
Close the VBE (Alt + Q or press the X in the top right corner).
Save file.

Test the code:

You can test the code by using excel rate built-in function vs my rateudf.
If the Excel built-in function starts to get #num! error, just put the result of the user defined rate in the guess rate of excel built-in function and it will correct the #num error.

Sample File:

RATEUDF.zip 9.7KB

Approved by mdmackillop

This entry has been viewed 233 times.