Financial Formula in Excel

Financial Formula in Excel

PMT Formula (Monthly EMI)

=-PMT(B2/12,B3*12,B1)

	A	B
1	Loan Amount	100000
2	Interest Rate P. A.	8%
3	Loan Period ( Year )	2
4
5	Monthly EMI	4,522.73

 

Monthly EMI (After Down Payment)

Loan Amount=B1-B2

=-PMT(B5/12,B6*12,B4)

	A	B
1	Car Price	500000
2	Down Payment	100000
3
4	Loan Amount	400000
5	Interest Rate P. A.	9%
6	Loan Period ( Year )	3
7
8	Monthly EMI	12,719.89

 

PV (Present Value) Formula

=-PV(B5/12,B6*12,C8)

	A	B
1	Car Price	500000
2	Down Payment	100000
3
4	Loan Amount	400000
5	Interest Rate P. A.	9%
6	Loan Period ( Year )	3
7
8	Monthly EMI	12,719.89
9
10	Present Value	400,000.00

 

=Pv(B2/12,B3*12,B1)

	A	B
1	PMT ( Monthly Installment )	4568.47
2	Interest Rate	9%
3	Period	2
4
5
6	Present Value	100,000

 

PPMT Formula (Principal Amount)

=-PPMT(B5/12,1,B6*12,B4)

	A	B
1	Car Price	500000
2	Down Payment	100000
3
4	Loan Amount	400000
5	Interest Rate P. A.	9%
6	Loan Period ( Year )	3
7
8	Monthly EMI	12,719.89
9
10	Present Value	400,000.00
11
12	Principal Amount	9,719.89

 

IPMT Formula (Interest Amount)

=-IPMT(B5/12,1,B6*12,B4)

	A	B
1	Car Price	500000
2	Down Payment	100000
3
4	Loan Amount	400000
5	Interest Rate P. A.	9%
6	Loan Period ( Year )	3
7
8	Monthly EMI	12,719.89
9
10	Present Value	400,000.00
11
12	Interest Amount	3,000.00

 

FV Formula (Future Value)

=-Fv(B2/12,B3*12,B1)

	A	B
1	SIP Amount	-10000
2	Interest Rate	12%
3	Period in Years	5
4
5	Future Value	816,696.70

 

=FV(B2/12,B3*12,B1)

	A	B
1	Premium	-5000
2	Rate	8%
3	Period	10
4
5	FV	72,432.81

 

Rate Formula (Interest Rate)

=Rate(B2*12,B3,B1)*12

	A	B	C
1	Loan Amount	100000
2	Period in Years	2
3	PMT	-4522.73
4
5	Interest Rate	0.67%	8.00%

 

NPER Formula (Period of Time)

 

=Nper(B2/12,B3,B1)/12

 

	A	B
1	Loan Amount	100000
2	Interest Rate	8%
3	PMT in Year	-4522.73
4
5	Period of Time	24