asked on

Bi-weekly payments with no interest charged in first period (formula?!)

I'm trying to reverse engineer the following payment options. These options come from a point of sale system and i do not have access to the calculation code.

(Principle is $1000, interest rate is 17% a year, calculated and added to the account monthly. Payments are due at the end of each period)

# Mnths	# Pmts	$ PMT	Interest
1	2	$500.00 $0.00 
2	4	$251.77 $7.03 
3	6	$169.02 $14.09 
4	8	$127.66 $21.18 
5	10	$102.84 $28.31 
6	13	$79.87  $38.25 
7	15	$69.65  $44.62 
8	17	$61.84 	$51.20 
9	19	$55.69 	$57.95 
10	21	$50.71 	$64.81 
11	23	$46.61 	$71.78 
12	26	$41.62 	$82.02

Open in new window

I think the part i am struggling with is that there is no interest charged when one month is selected (and this may carry through the other options).

With the choices of months 2-12, the total interest charged (as shown above) is less than if you calculate it from: Total interest = #pmts * $pmt - $1000.

Essentially, I am looking for assistance in determining how these amounts are calculated.

Using the PMT function in excel, I can calculate the following:

Pmt	Int
504.91   $9.82 
254.10	$16.40 
170.50	$23.01 
128.71	$29.65 
103.63	$36.31 
80.49	$46.37 
70.21	$53.10 
62.35	$59.87 
56.14	$66.66 
51.12	$73.49 
46.97	$80.34 
41.95	$90.67

Open in new window

But these values are different from the original schedule.

TommySzalapski

You should be able to just take the 2 payments from the first month and count them as if they were a downpayment (i.e. subtract them from the loan amout).
I'll pull up Excel and play around to see if I can confirm this.

ASKER CERTIFIED SOLUTION

d-glitch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TommySzalapski

Yes. If you subtract the loan amount by the sum of the first month's payments and subtract 2 from the number of payments, you get the exact same answer as your sample. So it is just treating the first month's payments as if they were the downpayment.

TommySzalapski

I agree with d-glitch. Since some months can have three payments and others will only have two, there most likely is not a way to condense it into a simple formula and the application you saw was most likely running a simulation of some kind.

bingie

ASKER

@D-Glitch,

That's excellent! Thank you for that. You mentioned the payment amount is usually

guessed

. Can you elaborate? Your example is right-on for the interest calculation... but how are they getting the payment starting point?

@TommySzalapski

Thanks for the reply. Can you elaborate on

If you subtract the loan amount by the sum of the first month's payments and subtract 2 from the number of payments, you get the exact same answer as your sample.

and post your sample excel?

Thanks...

TommySzalapski

For example, for line 5 I did this (the PMT formula does not work when you include months that have 3 payments). Since all the 5 months have 2 payments, just multiply by 2.
=PMT(0.17/12,4,1000-102.84*2,0,0)/2

TommySzalapski

Here is a quick sample of a simulator. Play with the payment field until the Payments and Months are what you want and the Min is very small (but under 0).
The gray shaded cells are auto calculated.
I don't have Excel so I hope it comes out okay (OpenOffice)
Note that the cents might be a bit off. I think they do the rounding differently.
sample.xls

d-glitch

You can guess the initial payment as just the Loan_Amt div by Num_Payments.

If you set up an Excel spread sheet to subtract payments and add interest charges over the course of a year, you can ask the Solver to adjust the payment to set the balance to zero after
any specific number of payments.

bingie

ASKER

Awesome!!!!

Thanks for everyone's help so far - I think i nailed it.

For example:

Taking 3 months/6 payments (Payment=$169.02 interest=$14.09)

1.) Guess the payment

$1000/6 = $166.67

2.) Simulate with that payment. Ending balance is 14.30 (the total interest).

Then, $14.30/6 payments= $2.38 per payment

New payment amount = $2.38+166.67 = $169.05

3.) Simulate again with $169.05 as the payment. Ending balance is now -0.20

New payment amount = $169.05 +(-.20/6) = $169.02

Total interest on this simulation is $14.10

Open in new window

This seems to be how they are doing it.

Is there a formula to use to calculate the interest instead of have to simulate the whole term? Excel or otherwise (pmt, nper, pv...etc)

Thanks!!

d-glitch

There are formulas for some financial quantities, but not if you mix days, weeks, and months.

http://answers.yahoo.com/question/index?qid=20080822070859AAY94ZT

SOLUTION

TommySzalapski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

d-glitch

A good explanation and an on line calculator here:
http://www.financeformulas.net/Loan_Payment_Formula.html

bingie

ASKER

Yeah, i'm familiar with those formulas. I used those originally with no success.

So, the question is - why

Thanks for the help. I'm going to poke some more at this.

I'll ask a followup if needed.

TommySzalapski

So, the question is - why

Interest is compounded monthly, so those formulas work out the monthly payment. With the bi-week system, some months have higher payments than others. Which months do this requires knowledge of a calendar (including leap years). Ouch! Again, I don't think it is possible to compute a formula for that.