Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF, Category, Data Type, Format, Using
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"? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A SUB CATEGORY USING DATA VALIDATION? ALRDY USNG DV FOR CATEGORY | Excel Discussion (Misc queries) | |||
find location of maximum and get the category type | Excel Discussion (Misc queries) | |||
Format Category Labels | Charts and Charting in Excel | |||
Format a column wihtout using "Format-Cells-Number-category-etc" | Excel Worksheet Functions | |||
Adding new 'Type' to Format->Number->Time->Type | Excel Discussion (Misc queries) |