Loan Amount 500,000.
00
Tenure 1 Period EMI
Interest Rate 9% 1 43,726
Monthly EMI ₹ 43,725.74 2 43,726
3 43,726
4 43,726
5 43,726
6 43,726
7 43,726
8 43,726
9 43,726
10 43,726
11 43,726
12 43,726
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0
0 0
0 0
0 0
0 0
0 0
Interest Paid Principal Amount Principal Remaining
3,750 39,976 460,024
3,450 40,276 419,749 Cum IPMT = This fnc will give y
3,148 40,578 379,171 paid between any give
2,844 40,882 338,289 -23735
2,537 41,189 297,101
2,228 41,497 255,603
1,917 41,809 213,794
1,603 42,122 171,672 Cum PRINC = this will retur
1,288 42,438 129,234 principal we have repaid
969 42,756 86,477 -413523
649 43,077 43,400
326 43,400 0
0 0 0
0 0 0
0 0 0
0. 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 Err:502 Err:502
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
IPMT = This fnc will give you the interest you have
paid between any given [Link] period
um PRINC = this will return the cummmulative
principal we have repaid for a given period
NPV IRR & MIRR
Cost of Capital 12% Cost of Capital 12%
Initial Investment 100000 Initial Investment -100000
1 25000 1 25000
2 25000 2 25000
3 25000 3 25000
4 25000 4 25000
5 25000 5 25000
NPV ₹ 90,119.41 IRR 7.93%
Actual NPV ₹ -9,880.59
For calculating IRR investment value should be in n
In excel it will only give you the present value of
future cashflows not the subtracted value. npv The re-investment is assumed that it is done at the
assumes that re-investment is done at the rate of cost
of capital the modified irr formula to overcome this:
Therefor, MIRR, is a better indicator than IRR in exc
13%
XIRR
investing in a stock
Dates Cashflow
1/1/2020 -10000
3/3/2020 -7000
6/15/2020 200
2/2/2021 -10000
6/15/2021 220
6/15/2021 230
8/22/2022 -12000
7/11/2024 45000
Value of the stock worth
XIRR 4.766% today
tment value should be in negative
umed that it is done at the rate of irr rate
to overcome this: The first date should be the lowest date,
er indicator than IRR in excel ascending order. It should have atleast one
positive and one negtive value
Asset Price 1400
Option Type Call
Strike Price 1300
Premium 60
Position Buy
Profit/Loss 40
Option Type Put Asset price Movements
Risk Free rate (p.a) 10% 0.008333 Period 0
Strike Price 1100 1000
Upward movement 15%
Downward movement 20%
Price
[Link] Periods 3
Expiry in months 3
Asset price 1000
Probability of up movement 0.595
probability of down movement 0.40476
Value of Option
Period 0
168.9537
Asset price Movements
1 2 3 0
1150 1322.5 1520.875
800 920 1058
640 736
512
Value of Option
1 2 3
82.2075 16.8595 0
300 180 42
460 364
588
NPV Initial Investment selection investment NPV Budget
Project 1 9 300,000.00
project 2 8 300,000.00
project 3 10 500,000.00
project 4 4 700,000.00
Project 5 10 400,000.00
Project 6 7 600,000.00
Project 7 9 600,000.00
Project 8 6 400,000.00
Project 9 9 300,000.00
Project 10 10 600,000.00
Project 11 8 300,000.00
Project 12 5 400,000.00
Project 13 8 600,000.00
Project 14 3 500,000.00
Project 15 5 500,000.00
64 92
Exclusion number of IPOs missing or incomplete after- 12
market price data
Remaining 80
Exclusion number of IPOs missing financial and other 9
issue specific information
Remaining total number of IPOs eligible for study. 71
Percentage of eligible companies in the sample for study 77%
21
Face Value 1000
Years to Maturity 5
Coupon Rate 8%
Frequency 2
Coupon Payment 40
Bond Price 1048.1291913252
Yield to Maturity 6.847%
YEAR Zero Yield Cash flow PV Maturity (Years)
0
0.5 0.068426 40 38.69793 0.5
1 0.0686998 40 37.42866 1
1.5 0.0689273 40 36.1941 1.5
2 0.0691156 40 34.99538 2
2.5 0.0692704 40 33.83305 2.5
3 0.0693969 40 32.70719 3
3.5 0.0694996 40 31.61752 3.5
4 0.0695822 40 30.56352 4
4.5 0.0696479 40 29.54447 4.5
5 0.0696996 1040 742.5474 5
5.5
6
6.5
7
7.5
8
8.5
9
9.5
10
10.5
11
11.5
12
12.5
13
13.5
14
14.5
15
15.5
16
16.5
17
17.5
18
18.5
19
19.5
20
20.5
21
21.5
22
22.5
23
23.5
24
24.5
25
25.5
26
26.5
27
27.5
28
28.5
29
29.5
30
30.5
31
31.5
32
32.5
33
33.5
34
34.5
35
35.5
36
36.5
37
37.5
38
38.5
39
39.5
40
40.5
41
41.5
42
42.5
43
43.5
44
44.5
45
45.5
46
46.5
47
47.5
48
48.5
49
49.5
50
Zero Coupon Yiled
0.0681 6.81
0.068426 6.8426
0.0686998 6.86998
0.0689273 6.89273
0.0691156 6.91156
0.0692704 6.92704
0.0693969 6.93969
0.0694996 6.94996
0.0695822 6.95822
0.0696479 6.96479
0.0696996 6.96996
0.0697396 6.97396
0.06977 6.977
0.0697926 6.97926
0.0698087 6.98087
0.0698198 6.98198
0.069827 6.9827
0.069831 6.9831
0.0698329 6.98329
0.0698331 6.98331
0.0698323 6.98323
0.0698309 6.98309
0.0698294 6.98294
0.0698281 6.98281
0.0698272 6.98272
0.069827 6.9827
0.0698276 6.98276
0.0698292 6.98292
0.0698319 6.98319
0.0698358 6.98358
0.069841 6.9841
0.0698474 6.98474
0.0698552 6.98552
0.0698643 6.98643
0.0698747 6.98747
0.0698864 6.98864
0.0698994 6.98994
0.0699138 6.99138
0.0699293 6.99293
0.0699461 6.99461
0.069964 6.9964
0.069983 6.9983
0.0700032 7.00032
0.0700244 7.00244
0.0700465 7.00465
0.0700697 7.00697
0.0700937 7.00937
0.0701186 7.01186
0.0701443 7.01443
0.0701707 7.01707
0.0701979 7.01979
0.0702257 7.02257
0.0702542 7.02542
0.0702832 7.02832
0.0703128 7.03128
0.0703429 7.03429
0.0703734 7.03734
0.0704044 7.04044
0.0704358 7.04358
0.0704675 7.04675
0.0704995 7.04995
0.0705318 7.05318
0.0705644 7.05644
0.0705972 7.05972
0.0706302 7.06302
0.0706634 7.06634
0.0706967 7.06967
0.0707302 7.07302
0.0707637 7.07637
0.0707973 7.07973
0.070831 7.0831
0.0708646 7.08646
0.0708983 7.08983
0.070932 7.0932
0.0709657 7.09657
0.0709993 7.09993
0.0710328 7.10328
0.0710663 7.10663
0.0710997 7.10997
0.071133 7.1133
0.0711662 7.11662
0.0711992 7.11992
0.0712321 7.12321
0.0712649 7.12649
0.0712975 7.12975
0.0713299 7.13299
0.0713622 7.13622
0.0713943 7.13943
0.0714262 7.14262
0.0714579 7.14579
0.0714894 7.14894
0.0715207 7.15207
0.0715517 7.15517
0.0715826 7.15826
0.0716132 7.16132
0.0716436 7.16436
0.0716738 7.16738
0.0717037 7.17037
0.0717334 7.17334
0.0717629 7.17629
0.0717921 7.17921
Par Value 1,000
Coupon rate 8.00% 800
Required Rate 11.25% 1125
YTM 2
Annual or semiannual Frequency of coupon payments 1
Coupon payment 80
Bond price ₹ 1,043.77
Liability
Buy a zero coupon bond therefore coupon payment will be zero
Amount Due 1000 Face value 1000
Due date 12/31/2025 maturity date 12/31/2025
Settlement Date 2/12/2024 Price 920
Duration 1.80033156
Yield of zero coupon bond 4.610%
Duration - modified 1.80033155822162
if you match the duration you can
match the required cashflows as well
We can use the same yield because there is only one payment in liability but if the person is making any in
possible to consider the same yiled
We're going to build asset portfolio with 2 different bonds
amount due 100,000 [Link] bonds
due date 12/31/2030 Face value
discount rate 6.50% coupon
time to maturity
duration 6.46322379 discount rate/yield
settlemet date
duration
weights
Portfolio duration
HOME WORK SUM
face value coupon maturity discount rate settlement
bond 1 100 6% 12/31/2026 5.50% 2/12/2024
bond 2 100 7% 12/31/2028 6.75%
bond 3 100 6.50% 12/31/2028 6.75%
bond 4 100 6.75% 12/31/2032 6.70%
bond 5 100 8% 12/31/2035 6.00%
bond 6 100 8.25% 12/31/2027 6.00%
bond 7 100 9% 12/31/2034 6.20%
bond 8 100 8.50% 12/31/2034 6.20%
portfolio duration
Liability
amount due due date discount rapresent value mduration weights
80000 6/14/2027 6% ₹ 213,840.96 3.14989517819706 0.108021881296654
45000 10/30/2029 7% ₹ 118,094.22 5.34267912772586 0.183220779705962
60000 12/31/2030 7.25% ₹ 156,742.51 6.41802641802642 0.220098526670232
50000 6/15/2031 6.90% ₹ 131,455.91 6.86778921110072 0.235522602805023
90000 12/31/2031 6.80% ₹ 237,054.16 7.38139825218477 0.253136209522129
325000 29.1597881872348
oupon payment will be zero
if I say this is the yield then this can be the required rate in the market
as well so I will use that to discount my liability
t if the person is making any intermediary payments then it won't be
the same yiled
with 2 different bonds
bond 1 bond 2
294.809521 705.190479
100 100 100000
7% 9%
12/31/2028 12/31/2035
6% 7%
2/12/2024 2/12/2024
4.04199464 7.47543458
29% 71% 100%
6.4632238
1 1
duration Weights quantity
2.57622139 0.0% 0
4.00379604 3.0% 98.10758
4.03351183 13.3% 433.8523
6.48224256 16.8% 546.4557
7.8571262 18.8% 609.6789
3.27004071 12.3% 398.7446
7.20916071 17.8% 579.8826
7.28300908 17.9% 583.2784
100%
6.217764
porfolio duration
0.3402576
0.97888984
1.41259816
1.61751959
1.86849917
6.21776436
BOND FUNCTIONS
PRICE 87.330229
PRICEDISC 85.0239562
PRICEMAT 88.4633279
ODDFPRICE 87.1736385
ODDLPRICE 98.2267393 OddLprice Oddfprice
TBILLPRICE 95.0138889 12/1/2018
YIELD 6.88% 2/5/2018 2/1/2019
YIELDDISC 2% 6/15/2018 2/15/2022
DURATION 2.80248183 2/15/2019
MDURATION 2.66903032 10/15/2017
TBILLYIELD 0.05277819
TBILLEQ 0.05267276
COUPDAYBS 7
COUPDAYS 182
COUPDAYSNC 175
COUPNCD 7/3/2024
COUPNUM 2
COUPPCD 1/3/2024
ODDFYIELD 0.06596507
Issue 1/1/2022
Settlement 1/3/2022 1/10/2024
Maturity 1/1/2025 1/3/2025 For t bill maturity should be 1 or less than 1 year.
First coupon 1/1/2022 7/1/2024 <-ODDFyield
Last coupon 1/1/2022
Frequesncy 2.00 Coupncd format is date
Coupon rate 5%
discount rate 5%
redemption 100 First coupon date is the date we get the first inte
YTM 10% Last coupon is the date when the bond paid its la
Bond Price 95.00 Annual coupon payment is 31st march of that ye
ould be 1 or less than 1 year. For coup days bs function it will return the days it is left before the coupon period.
the date we get the first interest payment
ate when the bond paid its last coupon before our settlement
ment is 31st march of that year That’s how it assumes
1) Currently, you sell 40,000 units of a product for $45 each. The unit variable cost of producing t
through 50 percent. Perform a sensitivity analysis to
Units 40000
Price 31.5
Variable cost per 5
Profit 1060000
1) Assume that at the end of each of the next 40 years, you put the same amount in your retireme
contribution from $5,000 throu
377,006.30 3% 4%
5000
10000
15000
20000
25000
A software development company is thinking of translating a software product into Sw
million. Translating the product into Swahili will increase sales during each of the ne
depends on the percentage incre
A software development company is thinking of translating a software product into Sw
million. Translating the product into Swahili will increase sales during each of the ne
depends on the percentage incre
If you earn interest at percentage rate r per year and compound your interest n times pe
$1 will grow
Amount 1
Interest Rate 10%
Colum N times per year 2
Row Time / Year 5
1.6288946267774
The Blue Yonder Airlines flight from Seattle to New York has a capacity of 250 people. The airline sold 270 tick
people show up for the flight, the flight is overbooked, and Blue Yonder must pay overbooking compen
230
69700
Sold 270
actual capacity 250
Row Show Up 260
Price 300 revenue 81000
Cost 30 total comp 3500
Profit 69700 cost 7500
profit 70000
If show up is greater than actual then sold * profit + sold * variable for over capacity - show u
variable cost of producing the product is $5. You are thinking of cutting the product price by 30 percent. You are sure this will in
rm a sensitivity analysis to show how profit will change as a function of the percentage increase in sales. Ignore fixed costs.
44000 48000 52000
1060000
me amount in your retirement fund and earn the same interest rate each year. Show how the amount of money you will have at ret
ribution from $5,000 through $25,000 and the rate of interest varies from 3 percent through 15 percent.
5% 6% 7% 8%
oftware product into Swahili. Currently, 200,000 units of the product are sold per year at a price of $100 each.
es during each of the next three years by some unknown percentage over the current level of 200,000 units. Sh
on the percentage increase in product sales. You can ignore the time value of money and taxes in your calculat
oftware product into Swahili. Currently, 200,000 units of the product are sold per year at a price of $100 each.
es during each of the next three years by some unknown percentage over the current level of 200,000 units. Sh
on the percentage increase in product sales. You can ignore the time value of money and taxes in your calculat
220,000.00
200,000.00 11,000,000.00
100.00
20.00
5,000,000.00
11,000,000.00
your interest n times per year, then in y years, $1 will grow to (1 + (r/n))ny dollars. Assuming a 10 percent ann
$1 will grow in 5–15 years for daily, monthly, quarterly, and semiannual compounding.
1.6289 5
Daily 365
Monthly 12
Quarterly 4
Semi Annualy 2
1
ople. The airline sold 270 tickets for the flight at a price of $300 per ticket. Tickets are nonrefundable. The variable cost of flying a passeng
ust pay overbooking compensation of $350 per person to each overbooked passenger. Develop a worksheet that computes Blue Yonder’s
240 250 260 270
230 240
69700
ble for over capacity - show up - capacity *350, or else sold - show up * variable + sold *270
e by 30 percent. You are sure this will increase sales by an amount from 10 percent
increase in sales. Ignore fixed costs.
56000 60000
the amount of money you will have at retirement changes as you vary your annual
ugh 15 percent.
9% 10% 11% 12%
ld per year at a price of $100 each. Unit variable cost is $20. The fixed cost of translation is $5
e current level of 200,000 units. Show how the change in profit resulting from the translation
of money and taxes in your calculations.
ld per year at a price of $100 each. Unit variable cost is $20. The fixed cost of translation is $5
e current level of 200,000 units. Show how the change in profit resulting from the translation
of money and taxes in your calculations.
240,000.00 260,000.00 300,000.00 320,000.00
dollars. Assuming a 10 percent annual interest rate, create a table showing the factor by which
annual compounding.
6 7 8 9
ndable. The variable cost of flying a passenger (mostly food costs and fuel costs) is $30 per passenger. If more than 250
p a worksheet that computes Blue Yonder’s profit based on the number of customers who show up for the flight.
250 260 270
Column Interest Rate 5000
Row Annual Contibution 3%
Year 40
200000
377,006.30
13% 14% 15%
340,000.00
10 11 12 13 14
15
1) A course offered by an institution is charged a fees of Rs 25,000. Of the fees collected 35% has to b
100 hours and the faculty handling the course are paid Rs 1000 per hour. The infrastructure expenses
students must enrol for the institution to break even on
Collected 25000 184615.4
Fee Paid 100000
Infrastructure 20000
Parent university fee 8750 64615.38
enroll 7.384615
Break even 0
1) Three electricians are wiring your new home. Electrician 1 by himself will need 11 days to do the job
herself will need 9 days to do the job. If all three electricians work on the jo
1 11
2 5
3 9
1) You have begun saving for your child’s college education. You plan to save $5,000 per year and wan
the college fund for annual rates of return on your investment from 4 percent through 12 p
1) You are borrowing $200,000 and making payments at the end of each month. For an annual interest
10, 15, 20, 25, and 30 years, determine the total inter
1) You are saving for your child’s college fund. You will contribute the same amount of money to the f
annual investment returns ranging from 4 percent through 15 percent and number of years investing v
1) You are saving for your child’s college fund. You will contribute the same amount of money to the f
annual investment returns ranging from 4 percent through 15 percent and number of years investing v
1) You can retire at age 62 and receive $8,000 per year or retire at age 65 and receive $10,000 per ye
annual rate at which you discount cash flows between 2 percent and
Retire at 62
Receive 8000
Rate 2%
ees collected 35% has to be paid to the parent university. The course is conducted for
e infrastructure expenses incurred by the institution stands at Rs 20,000. How many
nstitution to break even on the course.
need 11 days to do the job. Electrician 2 by himself will need 5 days to do the job. Electrician 3 by
electricians work on the job, how long will the job take to complete?
Amount per year
e $5,000 per year and want to know the amount of money you will have in
om 4 percent through 12 percent after saving for 10–15 years. Rate
Years
nth. For an annual interest rate ranging from 5 percent through 10 percent and loan durations of
, determine the total interest paid on the loan.
amount of money to the fund at the end of each year. Your goal is to end up with $100,000. For
umber of years investing varying from 5–15 years, determine your required annual contribution.
amount of money to the fund at the end of each year. Your goal is to end up with $100,000. For
umber of years investing varying from 5–15 years, determine your required annual contribution.
nd receive $10,000 per year. What is the difference (in today’s dollars) between these two choices as you vary the
ws between 2 percent and 10 percent and vary age of death between 70 and 84
65
10,000
5000 -60030.5356148 4%
4% 10
10 15
₹ -60,030.54
Borrowed 200000 ₹ 71,735.92 5%
Annual interest 6% 10
Loan Duration 10 15
₹ -66,449.20 20
25
30
Savings @ end of each year 9,036 Goal seek cell
[Link] Years 15
Return rate 15%
Goal ₹ -429,938.32
-429938.322615
5
10
15
5% 6% 7% 8% 9% 10% 11%
6% 7% 8% 9% 10% 1
2
3
4
5
6
7
8
9
10
4% 5% 6% 7% 8% 9% 10%
12%
IMPT PPMT
₹ 12,000.00 ₹ 15,173.59
₹ 11,089.58 ₹ 16,084.01
₹ 10,124.54 ₹ 17,049.05
₹ 9,101.60 ₹ 18,071.99
₹ 8,017.28 ₹ 19,156.31
₹ 6,867.90 ₹ 20,305.69
₹ 5,649.56 ₹ 21,524.03
₹ 4,358.12 ₹ 22,815.47
₹ 2,989.19 ₹ 24,184.40
₹ 1,538.13 ₹ 25,635.46
₹ 71,735.92
11% 12% 13% 14% 15%