Find Annual Interest Rate for EMI

[Muthukrishnan V]

Office 365

Find Annual Interest rate for EMI (Equitable Monthly Instalment)

Loan Principal amount is in Cell A5, say 2500000
Monthly EMI instalment is in Cell A6, say 45000
Period of loan (in months) is in Cell A7, say 60

My requirement is to find the Annual Interest Rate.
Kindly advise me formula.

Thanking you

 

[Sufiyan97]

Try

Book3
	A	B	C
4
5	2500000	Loan
6	45000	Monthly Installment
7	60	Period of Loan (Months)
8	2700000	Total Repayment
9	200000	Total Interest
10	0.08	Total Interest %
11	0.016	Interest Per Year
12
Sheet2

Cell Formulas
Range	Formula
A8	A8	=A6*A7
A9	A9	=A8-A5
A10	A10	=A9/A5
A11	A11	=A10/5

 

[Dave Patton]

Try Excel's functions
Rate will yield the rate
PMT should confirm the rate
You could also build a schedule to confirm the calculations.

 

[Dave Patton]

N.B. You must ensure the calculations are consistent with the legal contract and with the laws in your jurisdiction.

T202112a.xlsm
	A	B	C	D
1
2	Loan	2,500,000
3	Monthly Payment	45,000
4	Period of Loan (Months)	60
5	Interest Rate	3.07%
6
7	Payment	$45,000.00
8
9	Payment #	Payment Amount	Interest	2,500,000.00
10	1	45,000.00	6,396.70	2,461,396.70
11	2	45,000.00	6,297.93	2,422,694.63
12	3	45,000.00	6,198.90	2,383,893.53
1d

Cell Formulas
Range	Formula
B5	B5	=RATE(B4,-B3,B2)*12
B7	B7	=-PMT(B5/12,B4,B2,0,0)
D9	D9	=B2
C10:C12	C10	=D9*$B$5/12
D10:D12	D10	=D9-B10+C10