Determine monthly payment in 12, 24, & 36 months

**Spanishearl** · 01-12-2013, 02:09 AM

I'm trying to build a debt repayment form in excel that is the same as the one found here since I haven't gotten a response back yet from the site owner about a downloadable version over the online one.

I've figured out how to build it for 12 months, but the 24 and 36 elude me. I'm not that sharp with excel, so I don't know a lot of the formulas or tools in it.

I may even have taken the long road on the 12 month formula, but I don't know.

Anyone know how I can build this? I've attached the document I'm building in, and I have some arbitrary figures in it currently. The 12 month is identical to the online form, but I'm always off by several dollars for 24/36....

Attachment 205992

Edit: I just tried the PMT function, and built the formula as a tut noted, and the figures are WAY off. Gail's way $1000 with 13% interest would cost $94.17 over 12 months to pay off (total of $1130.04), and the PMT function says $168.99/mth (totalling $2027.88). The PMT function is way way off. I took a loan a couple years ago for $1000 and it cost (at that time) $88/mth to pay back over 12 months, so the $94.17 looks to be right, not pay double...

**huuthang_bd** · 01-12-2013, 03:09 AM

Hi Spanishearl,

I can't download you attached. Please upload againt.

Regards
huuthang

**Spanishearl** · 01-12-2013, 03:39 AM

Originally Posted by huuthang_bd

Hi Spanishearl,

I can't download you attached. Please upload againt.

Regards
huuthang

Odd, I can. Sorry about that. Here it is again.

(Maybe try a different browser? Works in IE)

MyBudget_1.xlsx

**huuthang_bd** · 01-12-2013, 05:49 AM

Hi Spanishearl,
I use PMT function but the result different result on the web in cases two and three years. Formulars i use are:
In cell F4

Please Login or Register  to view this content.

In cell G4

Please Login or Register  to view this content.

In cell H4

Please Login or Register  to view this content.

May be that web not correct.

**Spanishearl** · 01-12-2013, 11:53 AM

Hmm, those came out to -$0.56 and -$0.57. I don't think those are right...

EDIT: Nevermind, I had to correct the formatting to match the page. They're the closest so far, but still a bit off. They're spot on only if there is a 0% interest rate.

Thanks! I can work with paying a couple dollars more than her sheet says over double like the tut I had found...

MoneyMaker · 01-13-2013, 12:57 AM

Originally Posted by Spanishearl

Edit: I just tried the PMT function, and built the formula as a tut noted, and the figures are WAY off. Gail's way $1000 with 13% interest would cost $94.17 over 12 months to pay off (total of $1130.04), and the PMT function says $168.99/mth (totalling $2027.88). The PMT function is way way off. I took a loan a couple years ago for $1000 and it cost (at that time) $88/mth to pay back over 12 months, so the $94.17 looks to be right, not pay double...

Gail is finding the loan repayment assuming simple interest is paid on principal of $1000. To find the total amount you pay with simple interest

FV = P(1+rt)
= $1,000 (1+13%)
= $1,000 x 1.13
= $1,000 x 1.13
= $1,130

Interest paid = Prt
= $1000 x 13% x 1
= $1000 x 0.13
= $130

Now compare this to when you have to pay compound interest, meaning that not only you pay interest on principal (loan amount) but you also pay interest on interest accrued in prior periods

Now the total amount you pay when interest is compounded is as follows assuming interest is compounded per month

FV = PV (1+r)^t
= $1,000 (1+13%/12)^12
= $1,000 (1+1.084%)^12
= $1,000 (1+.01084)^12
= $1,000 (1.01084)^12
= $1,000 (1.138)
= $1,138

Interest paid = $1,138 - $1,000
Interest paid = $138

As you can see you have to pay $8 in interest with compound interest as compared to simple interest

Excel PMT function will calculate periodic monthly payment when interest is compounded discretely. Using this Excel PMT function calculator at this site thinkanddone.co.uk you will find the periodic payment of $-89.32

The negative sign means it is an outgoing cash flow

You would enter the following data

RATE = 13/12
NPER = 1*12
FV = 0
PV = 1000
TYPE = 0 for end of period payment