Loan Calculator

RhysBillOfficial

New Member
Joined
Aug 25, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.

ABC
1Loan Amount:20,000.00
2Interest Rate:10
3Months to Pay:3
4Amount 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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
2,232
Office Version
  1. 365
Platform
  1. Windows
Have a look to see if this is what you want:
Book2
ABCDEFGHIJ
1Loan Amt20000PaymentsPrincipalInterest ea 2-wk periodPrincipal+InterestPmtPrincipal after Pmt
2Interest Rate (per yr)10%12000083.3333333320083.33333($3,382.11)16701.22
3Term (mo)3216701.2269.5884185516770.80887($3,382.11)13388.70
4Payments per month2313388.755.7862332913444.48222($3,382.11)10062.37
5Amt each payment($3,382.11)410062.3741.9265389310104.29588($3,382.11)6722.18
656722.18328.009095846750.192098($3,382.11)3368.08
763368.07914.03366343382.112881($3,382.11)0.00
Sheet1
Cell Formulas
RangeFormula
F2F2=C1
G2:G7G2=$C$2/12/$C$4*F2
H2:H7,J2:J7H2=SUM(F2:G2)
I2:I7I2=$C$5
F3:F7F3=J2
C5C5=PMT(C2/12/C4,C3*C4,C1)
 

RhysBillOfficial

New Member
Joined
Aug 25, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 9, 2003
Messages
2,232
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,176,607
Messages
5,903,997
Members
435,066
Latest member
hannahexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top