Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating First UDF
I want to create a new User Defined Function to calculate compound interest on a loan or investment. The formula is: *F = P(1+R/N) ^ (N*T)* Whe F = Final Amount P = Initial Amount R = Interest Rate N = Number of Compounding Periods T = Time (in years) I have attempted this on a number of occassions and wind up with a mess and not every close to what the result to be. A lot of times I get an error. I'd appreciate any help I could get with this. Thank you. -- Larryh320 ------------------------------------------------------------------------ Larryh320's Profile: http://www.excelforum.com/member.php...o&userid=24722 View this thread: http://www.excelforum.com/showthread...hreadid=382900 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating First UDF
Hi Larry,
Larryh320 wrote: I want to create a new User Defined Function to calculate compound interest on a loan or investment. The formula is: *F = P(1+R/N) ^ (N*T)* Whe F = Final Amount P = Initial Amount R = Interest Rate N = Number of Compounding Periods T = Time (in years) Have you tried the built-in FV function? This should do what you're looking for. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating First UDF
Have you looked at the built-in Financial functions to see if they meet your
need? Try something like this: Function payAmount(P As Double, N As Integer, R As Double, T As Integer) R = R * 0.01 ' Convert to % payAmount = P * (1 + (R / N)) ^ (T * N) End Function Sub test() MsgBox payAmount(100.00, 12, 5.0, 5) End Sub Or =payamount(A1,B1,C1,D1) where A1=P, B1=N, C1=R, D1=T A1=100.00 B1=12 C1=5.0 D1=5 HTH "Larryh320" wrote: I want to create a new User Defined Function to calculate compound interest on a loan or investment. The formula is: *F = P(1+R/N) ^ (N*T)* Whe F = Final Amount P = Initial Amount R = Interest Rate N = Number of Compounding Periods T = Time (in years) I have attempted this on a number of occassions and wind up with a mess and not every close to what the result to be. A lot of times I get an error. I'd appreciate any help I could get with this. Thank you. -- Larryh320 ------------------------------------------------------------------------ Larryh320's Profile: http://www.excelforum.com/member.php...o&userid=24722 View this thread: http://www.excelforum.com/showthread...hreadid=382900 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating First UDF
The UDF should go in a VBA module. It seems quite trivial so I hope I
am addressing your actual problem. Function loan(P As Double, r As Double, n As Double, t As Double) As Double loan = P * (1 + r / n) ^ (n * t) End Function Larryh320 wrote: I want to create a new User Defined Function to calculate compound interest on a loan or investment. The formula is: *F = P(1+R/N) ^ (N*T)* Whe F = Final Amount P = Initial Amount R = Interest Rate N = Number of Compounding Periods T = Time (in years) I have attempted this on a number of occassions and wind up with a mess and not every close to what the result to be. A lot of times I get an error. I'd appreciate any help I could get with this. Thank you. -- Larryh320 ------------------------------------------------------------------------ Larryh320's Profile: http://www.excelforum.com/member.php...o&userid=24722 View this thread: http://www.excelforum.com/showthread...hreadid=382900 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating Yes,No | Excel Discussion (Misc queries) | |||
creating a pdf | Excel Discussion (Misc queries) | |||
Creating a Log | Excel Programming | |||
Creating add-ins | Excel Programming | |||
Help with creating a VBA | Excel Programming |