UDF, Category, Data Type, Format, Using
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"?
|