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"?

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"?

Bob,
Thanks for your response.
I was wondering where you saw my post. I can't find it on
microsoft.public.excel.programming which is where I thought
I had posted it. Anyway, I can let go of the part about
making the function available in the Financial category.
What matters more to me is the way it displays. The result
shows up as 1323.0206 but I want $1,323.02 without having
to format the cell. I thought making the function's
data type currency would do that.

JohnSCPierce

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

John,

You are getting the currency worksheet format confused with the currency
data type. The former is in the form £ #,##0.00, whereas this is how defines
the currency data type '... Currency variables are stored as 64-bit (8-byte)
numbers in an integer format, scaled by 10,000 to give a fixed-point number
with 15 digits to the left of the decimal point and 4 digits to the right.
This representation provides a range of -922,337,203,685,477.5808 to
922,337,203,685,477.5807. ...'.

If you don't mind the result as a string (that is. no math on it), you could
try

Public Function PandI(Principal As Long, _
AIR As Single, _
Years As Integer, _
Optional n As Integer = 12) As String
PandI = Format((Principal * AIR / 100 / n * (1 + AIR / 100 / n) _
^ (n * Years)) / ((1 + AIR / 100 / n) ^ (n * Years) - 1), "$#,##0.00")
End FunctionPublic Function PandI(Principal As Long, _
AIR As Single, _
Years As Integer, _
Optional n As Integer = 12) As String
PandI = Format((Principal * AIR / 100 / n * (1 + AIR / 100 / n) _
^ (n * Years)) / ((1 + AIR / 100 / n) ^ (n * Years) - 1), "$#,##0.00")
End Function

otherwise you need to format the cell as currency.

--

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
...
Bob,
Thanks for your response.
I was wondering where you saw my post. I can't find it on
microsoft.public.excel.programming which is where I thought
I had posted it. Anyway, I can let go of the part about
making the function available in the Financial category.
What matters more to me is the way it displays. The result
shows up as 1323.0206 but I want $1,323.02 without having
to format the cell. I thought making the function's
data type currency would do that.

JohnSCPierce

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Why use a UDF? Why not just use the PMT function?

=PMT(4.875%/12,30*12,-250000) will return 1323.02

If you don't want to format the cell (for whatever reason), you can use the
Text function to generate the string, eg

=TEXT(PMT(....),"$#,###.00") will return $1,323.02

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"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"?