# CASE-73M - IIM LUCKNOW - Excel

Document Sample

```					                                                    CASE-73M

This case reviews the following discounted cash flow (DCF) techniques:
lump sums, annuities (both ordinary and due), uneven cash flow streams,
intra-year compounding and effective annual rates, continuous
compounding, and amortization.

The model automatically performs the DCF analyses on the basis of cash
flow and interest rate parameters entered in the INPUT DATA section.
The model contains two graphs: (1) a plot of FV versus N at different
interest rates and (2) a graph of PVs.

Note that the model stretches out to column K.

The following cells have been blanked out:
C77,C78,C113,C114,B122-E125
======== ========= ================ ========= ========= ========= ==========================

INPUT DATA:                                       KEY OUTPUT:

Lump Sum Analysis:                                Lump Sum Analysis:

Beginning amount                \$18,000.00        Future value                  \$0.00
Ending amount                   \$35,000.00        Present value                 \$0.00
Interest rate                        8.40%        Effective rate                8.40%
Number of years                           6       Implied k                    11.72%
# Periods per year                       1

Annuity Analysis:                               Annuity Analysis:

Payment                          \$3,000.00        Future value              \$24,098.34
Annuity type:                                     Present value             \$14,852.91
Ordinary=0, due=1                        1       Effective rate                 8.40%
Interest rate                        8.40%
Number of payments                        6
# Payments per year                      1
# compound. per pay                      1

Uneven Payment Analysis:                       Uneven Payment Analysis:
Interest rate                        8.40%
# Periods per year                       1        Future value              \$21,119.79
CF year         0                \$1,000.00        Present value             \$13,017.09
1                 1,500.00        Effective rate                 8.40%
2                 2,000.00

Page 1
CASE-73M

3                 0.00       Continuous Compounding Analysis(lump sum):
4             3,500.00
5             4,500.00      Future value                                                  \$0.00
6             5,500.00      Present value                                                 \$0.00
Effective rate                                                8.76%
Amortization Schedule:

Interest rate                    10.20%
Loan amount                  \$35,000.00
Number of periods                      4

======== ========= ================ ========= ========= ========= ==========================

MODEL-GENERATED DATA:

Lump Sum Analysis:                         Future Value Data Table:                                          ---->

One year                                                               ---------------- ---------------------------------------------
Future value                 \$19,512.00                           n            k                     k
Present value                \$32,287.82                       ------         ----------                   ----------
0%                    5%
Based on input data                                            0            \$18,000                     \$18,000
Effective rate                     8.40%                       2            \$18,000                     \$19,845
Future value                                                   4            \$18,000                     \$21,879
Present value                                                  6            \$18,000                     \$24,122
Implied k for                                                  8            \$18,000                     \$26,594
input FV and PV                   11.72%                     10            \$18,000                     \$29,320

Present Value Data Table:                                         ---->

---------------- ---------------------------------------------
n            k                     k
------         ----------                   ----------
0%                           5%
0           \$35,000                     \$35,000
2           \$35,000                     \$31,746
4           \$35,000                     \$28,795
6           \$35,000                     \$26,118
8           \$35,000                     \$23,689
10           \$35,000                     \$21,487

Annuity Analysis:

Effective rate                    8.40%
Future value                 \$24,098.34
Present value                \$14,852.91

Uneven Payment Stream Analysis:

Page 2
CASE-73M

Effective rate                    8.40%
Future value                 \$21,119.79
Present value                \$13,017.09

Continuous Compounding Analysis:
(Uses input from INPUT DATA: lump sum section.)

Effective rate                         8.76%
Future value
Present value

Amortization Schedule:
Repay
of     Remaining
Year    Payment       Interest             Prin      Balance
----    -------       --------            ------     -------
1
2
3
4
-------   ------           -------
\$0             \$0          \$0
======== ========= ================ ========= ========= ========= ================

Page 3
CASE-73M

==========

Page 4
CASE-73M

========== ========= ========== ========== ==========

---->              ---->             ---->
Future Value
-----------------   ---------------- ------------------ ----------------- -----------------
k                 k                  k                 k                 k
----------         ----------         ----------        ----------        ----------
8.40%               10%               15%                20%               25%
\$18,000           \$18,000            \$18,000            \$18,000           \$18,000
\$21,151           \$21,780            \$23,805            \$25,920           \$28,125
\$24,854           \$26,354            \$31,482            \$37,325           \$43,945
\$29,204           \$31,888            \$41,635            \$53,748           \$68,665
\$34,317           \$38,585            \$55,062            \$77,397         \$107,288
\$40,324           \$46,687            \$72,820          \$111,451          \$167,638

---->              ---->             ---->
Present Value
-----------------   ---------------- ------------------ ----------------- -----------------
k                 k                  k                 k                 k
----------         ----------         ----------        ----------        ----------
8.40%                10%                15%               20%               25%
\$35,000           \$35,000            \$35,000            \$35,000           \$35,000
\$29,786           \$28,926            \$26,465            \$24,306           \$22,400
\$25,348           \$23,905            \$20,011            \$16,879           \$14,336
\$21,572           \$19,757            \$15,131            \$11,721            \$9,175
\$18,358           \$16,328            \$11,442             \$8,140            \$5,872
\$15,623           \$13,494              \$8,651            \$5,653            \$3,758

Page 5
CASE-73M

========= =========
END

Page 6
LSPV

PRESENT VALUES
For Different I and N

\$35,000

\$30,000

\$25,000
Present Value

\$20,000

\$15,000

\$10,000

\$5,000

\$0
0   2             4                        6               8   10
Number of Years (N)

0%     5%         10%        15%       20%   25%

Page 7
LSFV

FUTURE VALUES
For Different I and N

\$160,000

\$140,000

\$120,000

\$100,000
Future Value

\$80,000

\$60,000

\$40,000

\$20,000

\$0
0   2             4                        6               8   10
Number of Years (N)

0%     5%         10%        15%       20%   25%

Page 8

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 3 posted: 5/11/2010 language: English pages: 8
How are you planning on using Docstoc?