Excel Financial Calculation & Its Meaning II

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 01stmonth then I have to put per=1
  • If I have to calculate interest for the 2ndmonth 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)

PPMTPrincipal 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 01stmonth then I have to put per=1
  • If I have to calculate interest for the 2ndmonth 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 1st  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

 

1 thought on “Excel Financial Calculation & Its Meaning II”

  1. 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

    Reply

Leave a Comment