Annual compound interest monthly calculation

SI and CI are easy concept to learn. I m writing about them because someone asked me a question with little twist which I thought worth sharing

In simple interest calculation interest is charged only on the principal whereas in compound interest calculation the interest gets added with the principal and interest is charged on principal plus accumulated interest.

SI = PRT                               >> interest at particular time

SI = P x (1+RT)                    >> interest plus principal at particular time (Total Amount)

CI = P x (1+R/N) NT                   >> Total Amount

P = Principal

R = Annual rate of interest

T = Time

N = Compounding period vis Annual(1), Semi Annual(2), Quarterly(4), Monthly(12), Daily(365)

An Example

Amount = 10000

Annual Interest rate = 12%

Time = 5 yrs

Image

 

Now the twist question, at annual compounding what is the value after  69 months

No. of completed years = 5 (5 x 12 = 60)

No. of months remaining  = 9 (69 – 60)

Convert months into yearly fraction = 9/12 = 0.75

Therefore total no. of years = 5 + 0.75 = 5.75

CI = 10000 x (1 + 12%) 5.75 = 19,186.85

 

one more twist, for quarterly compounding what is the value for 71 months

one quarter has 3 months

No. of completed quarters = 23 (3 x 23 = 69)

No. of months remaining = 2 (71 – 69)

Convert months into quarterly fraction = 2/3 = 0.67

Therefore total no. of quarters = 23 + 0.67 = 23.67

CI = 10000 x (1 + 12%/4) 23.67  = 20,130.62

An excel problem (dynamic sum)

A friend of mine had this problem, data downloaded from ERP system contained duplicate value error which need to apportioned on quantity basis, the data set was fairly large.

Image

the solution I gave him involve helper column(s) , I myself believe its not the optimal solution but still sharing if anyone can come up with better solution

1> apply filter on data set and arrange “Total value” data in descending order

2>apply conditional formatting > highlight cells > duplicate values

this will show all duplicate values together in different color

3> add six helper columns (yes six,  you can reduce no. of columns later with better formulas , i have used them for better understanding)

helper1: apply “=COUNTIF($C$2:$C$92,C2)” this will count number of duplicates of particular values

helper2: apply “=IF(C2=C1,0,1)” this will help us indicate where values are changing

helper3: apply “=F2*G2” this will give number of times a unique value is repeated

helper4: apply “=SUM(D2:OFFSET(D2,H2-1,0))” this will sum the quantities corresponding to a Value

spend some time here on learning this formula, its called dynamic sum ranges, very helpful

helper5: apply “=IF(G2=1,I2,J1)” this will fill the total number of quantity in column corresponding to a value

helper6: apply “=C2*D2/J2” this will give you the desired values

Image

copy paste special values the helper6 column values in total values column

remove conditional formatting from total value column

arrange S.No column ascending order

there you are

better solution invited

link to file : https://drive.google.com/file/d/0B2FLZ9YjqpJTYXh2cUV4WGx0Vzg/edit?usp=sharing

 

Economic Value Added

It appeared in HUL Annual Report 2012-13. Found it worth sharing.

EVA is residual income after charging the Company for the cost
of capital provided by lenders and shareholders.It represents the
value added to the shareholders by generating operating profits
in excess of the cost of capital employed in the business.

EVA will increase if:
a. Operating profits can be made to grow without employing
more capital, i.e. greater efficiency.
b. Additional capital’s invested in projects that return more
than the cost of obtaining new capital, I.e. profitable growth.
c. Capital is curtailed in activities that do not cover the cost of
capital, i.e liquidate unproductive capital.

EVA = Net Operating Profit after taxes (NOPAT) – Cost of Capital Employed (CoCE)

NOPAT is a company’s after- tax operating profit for all investors, including shareholders and debt holders or EBIT x (1-Tax%)

CoCE = Weighted Average Cost of Capital (WACC) x Average
Capital Employed

where WACC=Ke*We +Kd*Wd*(1-Tax%)

Ke -cost of equity,  Kd – cost of debt

Wd – weight of equity, Wd – Weight of debt

Capital Employed = Debt+Equity or NWC + NFA or Total Assets – CL

EVA1