savings and loan calculations?

[jim lee]

I have two "what if"s I need to calculate..

The customer wants to purchase an item with price (P) and can only afford a monthly or weekly payment of (X).

He can start savings at interest (S) or take out a loan at interest (L).

How long would it take and how much would it cost for either choice?

I'm looking for the two functions..

How many payments of (X) to a saving goal to (P)

-and-

How many payments of (X) to pay off a loan of (P)

-given the other paramiters.

Anyone know where I can come up with these two functions?

Many thanks in advance!!

-jim lee

[comment]

To find out the number of payments to pay off a loan:

( Log ( payment ) - Log ( payment - presentValue * rate ) ) / Log ( 1 + rate )

Note:

1. All numbers need to be positive.

2. Make sure your rate matches the chosen period - i.e. if the yearly rate is 12%, and the payments are monthly, the actual rate should be 0.12 / 12 = 0.01.

3. You might want to Ceiling() the result to get a whole number of payments.

To find out the number of payments needed to reach a given sum is more difficult. I think this would require an iterative calculation using the FV() function. You'd raise the number of payments until the result reaches or exceeds the goal.

Such calculation is not very practical in any case. By the time the goal has been reached, the price may change.

[Lee Smith]

Take a look at this Tech Article at FMI site about Future Values, and see if doesn't help.

HERE

HTH

Lee

Edited November 1, 2006 by Guest
clean up

[Raybaudi]

Such calculation is not very practical in any case. By the time the goal has been reached, the price may change.

... or the item will be "out of production" :P

[jim lee]

->I think this would require an iterative calculation<-

I think that's the key. Maybe I'll try writing a recursive function. Basically step through the loan or savings process calculating the value each pay period until they go to completion.

Thanks!

-jim lee