Excel Financial Calculation & Its Meaning II

Introduction

In our previous series of Financial Calculation through MS-Excel tools , we covered

• COUPDAYBS
• COUPDAYS
• COUPDAYSNC
• COUPNCD
• COUPNUM
• COUPPCD

In this Post Excel Financial Calculation & Its Meaning II, We will cover some financial terms like

• PMT, IPMT, PPMT
• CUMIPMT
• CUMPRINC
• DB
• DDB

                                                                     

MS-Excel Terms & Its Meaning

11)

PMT,IPMT,PPMT

PMT:– This function is used to calculate the periodic payment for a loan (that has constant payment and constant interest rate).

Eg :

We take Personal Loan amount Rs.100000 for interest rate of 14% annum  for a period of 4 yrs

Loan Amount	100000
Interest rate	14% per annum =convert into month =14%/12
Loan period in a year	4 years = convert into month =4*12= 48 months

So, we have to calculate Installment (EMI) per month

          Note: whenever, there is a value under bracket, is considered as optional

=PMT(rate,nper,pv,[fv],[type])

=PMT(14%/12,4*12,100000) = Rs.2732.65 (you have to pay Rs.2732.65/month as an installment).

Note:

[fv]= Optional. Future value or cash balance you want after the last payment is done.

Default value is 0.

[type]: optional. Default is 0.

0= end of the month

1= beginning of the month.

Installment= Principal+ Interest

IPMT = Interest on Per month Transaction. This function is used to calculate Interest paid per month on paying Installment .

Syntax:

              =IPMT(rate,per,nper,pv,[fv],[type])

Per=period 

• If I have to calculate interest for the 01^stmonth then I have to put per=1
• If I have to calculate interest for the 2^ndmonth then I have to put per=2

             and so on

=IPMT(14%/12,1,4*12,100000) = Rs.1166.67 ( you have to pay an Interest of Rs.1166.67 on the first month  on an Installment of Rs.2732.65)

PPMT: Principal per month Transaction. This function calculates the principal amount on your Installment paid per month.

 

Syntax:

                   =PPMT(rate, per, nper, pv,[fv],[type])

=PPMT(14%/12,1,4*12,100000) = Rs.1565.98 (The principal

you paid is Rs.1565.98  on the first month on an Installment

of Rs.2732.65).

Now, Calculate

                                   PMT= IPMT+PPMT

1166.67+1565.98 = Rs.2732.65

 

12) 

CUMIPMT: It means Cumulative Interest on Per month Transaction. This function will give you (return)

  Cumulative (total ) interest paid on a loan amount between any two loan payment period . For eg: If you want to know the total interest paid between 6 month to 10 month (6+7+8+9+10 months). Here, 6 month is the start period and 10 months is the End period.

Syntax:

                =CUMIPMT(rate, nper, pv, start period, end period, type)

 

Rate	Interest rate per month
nper	Total number of installment (EMI)=number of period per month= (nper*12)
pv	Present value  or Loan Amount
Start period	You have to calculate interest amount from which month . For eg : I have to calculate interest amount from 6 months then : Start period=6 months
End period	you have to calculate interest amount upto this month. For eg: I have to calculate interest amount upto 10 months , then End period=10 months
type	It indicates the interest amount to be calculated at the end of the month or beginning of month. You have to put 0 for end of the month You have to put 1 for beginning of the month

 

Calculation

Let us calculate for Home Loan

 

Loan Amount :  5000000

Interest rate: 8% = 8%/12 = 0.7%

Period (years) : 10 = 10*12 = 120 months

 

 =CUMIPMT(rate,nper,pv,start period,end period,type)

                             (8%/12 , 10*12, 5000000,6,10,0)

                              =168306.2438

 

Loan Amount	5000000
Interest Rate(Annual)	8% annum (convert into monthly =  8%/12= 0.7% monthly)
Period(years)	10 years (convert into month= 10*12= 120 months)
=CUMIPMT(rate, nper, pv, start period, end period, type)	=CUMIPMT(0.7%,120,5000000,6,10,0)= 168306.2438 Start period: 6months= I have to calculate interest amount from 6th months End period: 10 months= I have to calculate interest amount  upto 10th months Type : 0 :- calculate interest at the end of the month

Let’s check the CUMIPMT calculation

Per=period 

• If I have to calculate interest for the 01^stmonth then I have to put per=1
• If I have to calculate interest for the 2^ndmonth then I have to put per=2

                   And so on

Month	CUMIPMT
1	=IPMT(rate,per,nper,pv,[fv],[type]) =IPMT(0.7%,1,120,5000000) =35000
2	=IPMT(0.7%,2,120,5000000)              =  34812.92
3	=IPMT(0.7%,3,120,5000000)              = 34624.53
4	=IPMT(0.7%,4,120,5000000)               = 34434.82
5	=IPMT(0.7%,5,120,5000000)               =34243.78
6	=IPMT(0.7%,6,120,5000000)                =34051.41
7	=IPMT(0.7%,7,120,5000000)                 = 33857.69
8	=IPMT(0.7%,8,120,5000000)                  = 33662.61
9	=IPMT(0.7%,9,120,5000000)                   =33466.17
10	=IPMT(0.7%,10,120,5000000)                  =33268.36
	If you calculate Total interest from 6 month upto 10 months you will get = 168306.24

 

13)

CUMPRINC: It gives (returns) the cumulative principal paid on a loan between two period (any specific period).

                       =CUMPRINC(rate, nper, pv, start period, end period,[type])

Let us calculate for Home Loan

Loan Amount	500000
Interest rate (annum)	12.50% (convert into interest per month = 12.50%/12 )
Period (years)	1 year (convert into months : 1*12 = 12 months)

Let us calculate the cumulative principal  (total principal paid)  between 1^st  to 10 months

PPMT: Principal per month Transaction. This function calculates the principal amount on your Installment paid per month.

                     =PPMT(rate, per, nper, pv,[fv],[type])

Period (month)	Principal PPMT	Start Period	End Period	Principal CUMPRINC
1	18445.32	1	10	=CUMPRINC(12.50%/12,24,500000,1,10,0)= 193344.06 ( If you add PPMT duration from 1 to 10 months , you will get  193344.06)
2	18637.46	1	24	=CUMPRINC(12.50%/12,24,500000,1,24,0)=500000
3	18831.60
4	19027.76
5	19225.97
6	19426.24
7	19628.60
8	19833.06
9	20039.65
10	20248.40
11	20459.32
12	20672.44
13	20887.78
14	21105.36
15	21325.21
16	21547.34
17	21771.79
18	21998.58
19	22227.74
20	22459.28
21	22693.23
22	22929.61
23	23168.46
24	23409.80

14)

DB—Also called Depreciation Balance. This function calculates (or gives or return) the  depreciation of an asset for a specific period using the fixed-declining balance method. For example : If we buy a computer worth Rs.20000 and after 6 years, the Salvage value (the cost which is again to be sold after completely using , here example :6 years) is Rs.2000. The DB  function will show you the Systematically declination of your goods (computer).

Syntax :

                                 =DB(cost, salvage, life, period,[months])

Note: whenever, there is a value under bracket, is considered as optional.

• Cost:–It is the cost of an asset
• salvage:– It is the value after the asset has been fully depreciated. [when you have used the asset completely and wanted to sell it again on some amount, so the value or amount which the asset will be sold is called Salvage value]
• life: the total life over which asset is depreciated.
• period:- It is the period  to calculate the linear depreciation. [you have to calculate depreciation on which period. either you have to calculate depreciation on the 01st period or 2nd period or 3rd period. Whatever period, you have to calculate you have to fill it]

Item	Cost	Salvage	Life	Year	DB
Computer	20000	2000	6 years	1	=DB(20000,2000,6,1)= 6380 (In the 1st year the cost of  goods depreciated by Rs.6380
				2	=DB(20000,2000,6,2)= 4344.78 (In the 2nd year the cost of goods depreciated by Rs.4344.78)
				3	=DB(20000,2000,6,3)= 2958.80
				4	=DB(20000,2000,6,4)= 2014.94
				5	=DB(20000,2000,6,5)= 1372.17
				6	=DB(20000,2000,6,6)= 934.45
					If you calculate all (6380+4344.78+2958.80+2014.94+1372.17+934.45)= Rs. 18005.14 Salvage = 20000-18005.14= 1994.86  (closer to the value)

15)

DDB:  It is also called Double declining Balance method. This function calculates (return or gives) depreciation of an asset for a given period using the double-declining balance method or another method you specify by changing the factor argument.

Syntax :  

                     =DDB(cost,salvage,life,period,[factor]

[factor]: optional. Rate at which the balance declines. If omitted, defaults to 2.

Item	Cost	Salvage	Life (in years)	Year	DDB
Ferrari	500000	50000	6	1	=DDB(500000,50000,6,1)  =166666.67 ( In the 01st yr cost of goods depreciated by Rs. 166666.67
				2	=DDB(500000,50000,6,2) =111111.11 ( In the 02nd yr cost of goods depreciated by Rs. 111111.11
				3	=DDB(500000,50000,6,3) =74074.07 ( In the 3rd yr cost of goods depreciated by Rs. 74074.07
				4	=DDB(500000,50000,6,4) =49382.72 ( In the 04th yr cost of goods depreciated by Rs. 49382.72
				5	=DDB(500000,50000,6,5) =32921.81 ( In the 05th yr cost of goods depreciated by Rs. 32921.81
				6	=DDB(500000,50000,6,6) =15843.62 ( In the 06th yr cost of goods depreciated by Rs. 15843.62
					If you calculate all (166666.67+111111.11+74074.07+49382.72+32921.81+15843.62= Rs.450000 Cost= Salvage +DDB = 50000+450000= Rs.500000