John,

Allocating the category as you show works okay for me. What is the error
text?

Also, you have to include the file name if you put it in Personal.xls. To
avoid this, you will need an addin.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Pierce" wrote in message
m...
I've written a UDF that calculates the P&I payment for a loan.

Public Function PandI(Principal As Long, AIR As Single,
Years As Integer, Optional n As Integer = 12) As Currency
PandI = (Principal * AIR / 100 / n * (1 + AIR / 100 / n)
^ (n * Years)) / ((1 + AIR / 100 / n) ^ (n * Years) - 1)
End Function
Whe
Principal might be: 250000
AIR (Annual Interest Rate) might be: 4.875
Years might be: 30
n (number of payments per year) is usually: 12
The result will be 1323.0206

I thought giving it data type currency would make it display as
$1,323.02 but that doesn't happen unless I format the cell.

The function is in my Personal.xls. It appears in the Paste
Function Dialog (Function Wizard) in Category:All as
Personal.xls!PandI.
Following instructions from a book, I put the following
procedure in a module in Personal.xls.
Public Sub Auto_Open()
Application.MacroOptions macro:="PandI", Category:=1
End Sub
but I get an error and it doesn't work.
Also, is it possible to make it so that the function can be
used on the worksheet by just typing "=pandi"?