# Determine monthly payment in 12, 24, & 36 months

1. ## Determine monthly payment in 12, 24, & 36 months

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...

2. ## Re: Determine monthly payment in 12, 24, & 36 months

Hi Spanishearl,

Regards
huuthang

3. ## Re: Determine monthly payment in 12, 24, & 36 months

Originally Posted by huuthang_bd
Hi Spanishearl,

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

(Maybe try a different browser? Works in IE)

MyBudget_1.xlsx

4. ## Re: Determine monthly payment in 12, 24, & 36 months

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.

5. ## Re: Determine monthly payment in 12, 24, & 36 months

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...

6. ## Re: Determine monthly payment in 12, 24, & 36 months

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

There are currently 1 users browsing this thread. (0 members and 1 guests)