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^{st}month then I have to put per=1
- If I have to calculate interest for the 2^{nd}month 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 6^{th} months End period: 10 months= I have to calculate interest amount upto 10^{th} 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^{st}month then I have to put per=1
- If I have to calculate interest for the 2^{nd}month 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 1^{st} year the cost of goods depreciated by Rs.6380 |
2 |
=DB(20000,2000,6,2)= 4344.78 (In the 2^{nd} 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 01^{st} 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 3^{rd} yr cost of goods depreciated by Rs. 74074.07 | ||||
4 | =DDB(500000,50000,6,4) =49382.72 ( In the 04^{th }yr cost of goods depreciated by Rs. 49382.72 | ||||
5 | =DDB(500000,50000,6,5) =32921.81 ( In the 05^{th} yr cost of goods depreciated by Rs. 32921.81 | ||||
6 | =DDB(500000,50000,6,6) =15843.62 ( In the 06^{th} 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 |
As indicated by the FSA, these commissions added up to a normal of 5.6% of the total contributed. So while monetary exhortation may be 'free at the retail location', it positively affects the exhibition of a speculation – and, all the more significantly, unmistakably the counsel given to the purchaser can never be genuinely unprejudiced. financial advice newcastle