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

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]

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

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

Creating First UDF
 

use it like this....

Function F(P, R, N, T)
F = (P * (1 + R / N)) ^ (N * T)
End Function



"Larryh320" a écrit
dans le message de news:
...

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