Custom functions

Function arguments with percentage and decimal format

Excel formulas accept percentage values in both decimal and percentage format. For example, 0.055, and 5.5% both return 0.055 when entered in a cell in Excel.

Consider the following formula for calculating the present value (PV) from the future value (FV) on an amount, at a discount rate (r) over (n) periods.

The custom function for this formaula is named PValue.

The syntax is: PValue(DiscRate, FValue, NoPeriods), where DiscRate is the interest rate per period, FValue is the future value, and NoPeriods is the number of periods.


					


					Function PValue(DiscRate As Double, FValue As Double, NoPeriods As Double) As Double

					     PValue = FValue * (1 + DiscRate) ^ -NoPeriods

					End Function

1. Calling the PValue custom function from Excel

Using PValue in the Excel formula bar is straight forward, because Excel automatically accepts percentage values in decimal and percentage format.

The same applies to a custom function entered via the Function Wizard on the Formulas tab. In this example, the discount rate (DiscRate) is entered as 7.5%, and is stored in computer memory as 0.075 as indicated by the arrow. Discrate 7.5% = 0.075.

2. Calling the PValue custom function from VBA

VBA does not automatically convert percentage values such as 7.5% to decimal format. The Sub procedure PValue_test is used to illustrate the differences under three different scenarios.

Scenario 1, the rate is in decimal format 0.075; Scenario 2, the rate is in percentage format 7.5%; and Scenario 3, the rate of 7.5% is passed with a variable of the type String. In each case, the output is sent to the Immediate Window with the Debug.Print statement.

Details of functions:
Replace: Returns a string in which a specified substring has been replaced with another substring a specified number of times.
Val: Returns the numbers contained in a string as a numeric value of appropriate type.


				


				Sub PValue_test()

				Dim ans As Double

				Dim dRate As String



				' Scenario 1 

				    ans = PValue(0.075, 115.5625, 2)

				    Debug.Print "1. PValue(0.075, 115.5625, 2) returns: " & ans & vbNewLine


				' Scenario 2 

				    ans = PValue(Replace("7.5%", "%", "") / 100, 115.5625, 2)

				    Debug.Print "2. PValue(Replace(""7.5%"", ""%"", """") / 100, 115.5625, 2) " & _

				"returns: " & ans & vbNewLine


				' Scenario 3 

				dRate = "7.5%"

				   ans = PValue(Replace(dRate, "%", "") / 100, 115.5625, 2)

				    Debug.Print "3. PValue(Replace(dRate, ""%"", """") / 100, 115.5625, 2) " & _

				"returns: " & ans & vbNewLine



				End Sub

In Scenario 1, the 0.075 is simply passed as a double to PValue and the return value is received by the calling procedure. In Scenario 2 (and 3), however, the 7.5% must be entered as a String and is enclosed with the "" expression. Normally, the Val function would be used to extract the leading number sequence from the string, however, it does not work in this situation. The problem is that the % character is treated as the VBA Type Declaration Character for an Integer. This means that if "7.5%" is passed to the Val function, it only returns the integer component 7. It also returns a Type mismatch error. To prevent this, the Replace function is used to strip out (replace) the % character with nothing "". So Replace("7.5%") / 100 returns 0.075.

The Immediate Window with details for each scenario is shown below.