# How to format the return value of a UDF

## 0. Apply format to custom function return value

- An example of the Excel
**FV**function with formatted return value - VBA code for UDF version
- Annuity formula
- Cash flow loop implementation
- Format return value: VBA.Format, and Font.Color

## 1. WS functions with formatted return values

Excel functions from the Financial Category return values with a currency format applied. Examples include FV, NPV, PV, and PMT. An example of FV, the future value of an annuity is shown in figure 1.

## 2. VBA code

### 2.1 Currency format

Using the VBA macro recorder, the code to format Currency $1,234.45; -$1,234.45 is:

Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

### 2.2 Formula for annuity

The formula for the future value (\(FV\)) of an ordinary annuity is the sum of compounded future cash flows:

$$\begin{equation} FV = \sum\limits_{j=1}^{n} A \times (1 + i) ^ {n - j} = A \sum\limits_{j=1}^{n} (1 + i) ^ {n - j} \end{equation}$$

where:

- \(A\) is the amount of the regular cash flow (payment)
- \(i\) is the periodic interest rate
- \(n\) is the number of regular cash flows (payments)

Equation 1 can be interpreted as the sum of a finite series, or equivalently, the partial sum of an infinite series. The sum \(S_n\) is:

$$\begin{equation} S_n = a \left( \frac{(1-r)^n }{1-r} \right) \end{equation} $$ Substituting \((1 + i)\) for the common ratio \(r\), and rearranging gives the familiar formula for the future value of an ordinary annuity:

$$\begin{equation} FV = A \times \left[ \frac{(1+i)^{n} - 1 }{i} \right ] \end{equation}$$

The syntax of the FV function is:

- FV(rate,nper,pmt,[pv],[type])

With equation 3, the relationship to the **FV** function is: \(\color{blue}{rate} = i\), \(\color{blue}{nper} = n\), and \(\color{blue}{pmt} = A\)

### 2.3 The xlfFV UDF

The annuity formula, equation 3 is implemented in code 1.

**Code 1:**VBA code for equation 3

Function xlfFV(rate As Double, nper As Double, pmt As Double) As String Dim tmp As Double tmp = pmt * ((1 + rate) ^ nper - 1) / rate ' equation 3 fssFV_2 = Format(-tmp, "Currency") If tmp > 0 Then Selection.Font.Color = vbRed Else Selection.Font.Color = vbBlack End Function

Currency format (the second part of the VBA Format function), code 1 line 8, and Color, code 1 line 9 are an approximation to the NumberFormat property.

The summation sequence, equation 1, is coded with a For...Next loop in code 2

**Code 2:**VBA code for equation 1 using a For...Next loop

Function fssFV_FNloop(rate As Double, nper As Double, pmt As Double) As String ' Returns the future value of an ordinary annuity Dim tmp As Double Dim j As Integer For j = 1 To nper tmp = tmp + pmt * (1 + rate) ^ (nper - j) ' equation 1 Next j fssFV_FNloop = Format(-tmp, "Currency") If tmp > 0 Then Selection.Font.Color = vbRed Else Selection.Font.Color = vbBlack End Function

**Development platform**: Office 365 ProPlus Excel 64 bit.**Published**: 10 May 2020**Revised:**Friday 24th of February 2023 - 03:13 PM, Pacific Time (PT)