Loan Calculator

[RhysBillOfficial]

Hello,

I am trying to create a loan calculator to determine the amount the borrower will pay on a bi-monthly basis.

I tried revising my formula many times but the result is different when I try to calculate it manually.

Here is the table below for reference.

	A	B	C
1	Loan Amount	:	20,000.00
2	Interest Rate	:	10
3	Months to Pay	:	3
4	Amount Payable	:	3500.00

The calculation should look like this.

20,000 / 3 months * 2 = result should be 3333.333
then 3333.333 + half of the interest rate and should provide a result of 3,500.00

But for some odd reason, I am getting a result of 3833.33 or 7166.67.

If someone could explain what I've done wrong, that would be appreciated.

Kind Regards,
@RhysBillOfficial

 

[KRice]

Have a look to see if this is what you want:

Book2
	A	B	C	D	E	F	G	H	I	J
1	Loan Amt		20000		Payments	Principal	Interest ea 2-wk period	Principal+Interest	Pmt	Principal after Pmt
2	Interest Rate (per yr)		10%		1	20000	83.33333333	20083.33333	($3,382.11)	16701.22
3	Term (mo)		3		2	16701.22	69.58841855	16770.80887	($3,382.11)	13388.70
4	Payments per month		2		3	13388.7	55.78623329	13444.48222	($3,382.11)	10062.37
5	Amt each payment		($3,382.11)		4	10062.37	41.92653893	10104.29588	($3,382.11)	6722.18
6					5	6722.183	28.00909584	6750.192098	($3,382.11)	3368.08
7					6	3368.079	14.0336634	3382.112881	($3,382.11)	0.00
Sheet1

Cell Formulas
Range	Formula
F2	F2	=C1
G2:G7	G2	=$C$2/12/$C$4*F2
H2:H7,J2:J7	H2	=SUM(F2:G2)
I2:I7	I2	=$C$5
F3:F7	F3	=J2
C5	C5	=PMT(C2/12/C4,C3*C4,C1)

 

[RhysBillOfficial]

Hello -

The amount payable should be displayed as ₱3500.00.

The calculation should go like this: ₱20,000 / ( 3 months * 2 ) = result should be ₱3333.333
then ₱3333.333 + half of the interest rate is ₱166.667.
And should provide a result of ₱3,500.00.

Thanks.

 

[KRice]

In post #1, what do you mean by "interest rate"? It seems that "10" really means 10%, or 0.10. But I assumed you meant 10% per year and that the loan terms specified compounding that corresponded to the payment interval (i.e., twice per month). These types of details are needed.

Your latest post where you show 166.667, suggests that the loan terms are 10% per month and there is no compounding, the interest is simple interest only...but I'm only guessing. Could you clarify please?