Create a function that returns the present value of a sum

Question

Assignment: User Defined Functions

This Project is a series of user-defined functions. Please "Insert" a module and develop these functions in the VBA module.

Use one tab to demonstrate your functions. Use cell references to assign values to your function and to display the value returned by your function. Save your spreadsheet as a "Macro-enabled" worksheet. Otherwise, Excel will strip the VBA code from your worksheet.

Use one tab. Print out the spreadsheet showing the inputs and outputs of your function. An assignment will be available on Sakai for submission of your .XLSM file.

Deliverables:

1) Printout of your VBA Module (may want to export and use Word to print)

2) Printout of your spreadsheet w/answers

3) Your spreadsheet (XLSM) file (through Sakai)

1) Create a function that calculates the Cost of Equity (Ke) using CAPM. CAPM is defined as:

K_e  = R_f + β(ERP )

This function should have the following characteristics:

a. Three parameters:

i. Risk Free Rate

ii. Equity Risk Premium

iii. Beta

b. Follow naming convention guidelines

c. Document (comment) the function

2) Create a function that calculates price of a share of stock using the single stage dividend discount model.  Single-Stage DDM is defined as:

P = {d₀ (1 + g )} / (k_e - g)

This function should have the following characteristics:

a. Three parameters:

i. Dividend in Year 0

ii. Growth

iii. Cost of Equity

b. Follow naming convention guidelines

c. Document (comment) the function

3) A beta can be adjusted to reflect the effects of leverage on the risk of corporate cash flows. This process is called "Un-levering" and "Levering" beta. Create two functions. One function will "un-lever" beta the other function will "Lever" it.

Un-levering Beta is performed as follows:

β_u = β_L / {1 + (D/E) (1 - T)}

Levering Beta is performed as follows:

Β_L = β_U {1 + (D/E) (1 - T)}

This function should have the following characteristics:

a. Three parameters:

i. Beta (Levered or Unlevered)

ii. Debt-to-Equity Ratio

iii. Corporate Tax Rate

b. Follow naming convention guidelines

c. Document (comment) the functions

4) The theoretical value of a bond is a simple calculation, described as the present value of an annuity and the present value of principle at maturity.

Fair Value of a Bond = C [{1 - [1 / (1 + i)ⁿ] / i}] + {P / (1 + i)ⁿ}

where:

C  = semiannual coupon payment ($)

n = number of periods (number of years x 2)

i = periodic interest rate (required yield divided by 2)

P = principal ($)

For this question you need to break the problem down into smaller, logical components.

There are essentially, two components to valuing a bond. There is the present value of coupons and the present value of principle.

The two components comprise calculating a value, such as periodic coupon, and multiplying by a present value factor. Therefore we can logically break a bond calculation down into three parts.

1) Calculate the present value annuity factor to discount the bond's coupons

2) Calculate the present value factor for discounting the principle

3) Calculate the bond's coupon and principle value and apply present value factors

Create a function that returns the present value of an annuity. The function should take two parameters:

a. Number of Periods

b. Interest Rate

Create a function that returns the present value of a sum. The function should take two parameters:

a. Number of periods

b. Interest Rate

Create a function to value a bond. Use the previous two functions created. The function should take five parameters:

a. Principal returned at maturity

b. Coupon Rate

c. Years to maturity

d. Annual Payment Frequency

e. Annual Discount Rate

5) A financial asset can be valued utilizing a valuation framework that allows for growth to be modeled as a two-stage process. The model follows the theory that a firm has a period of high growth for a limited period time and then exhibits a lower; some consider it a mature, rate of growth in perpetuity.

The model is called the "H Model". Its formula is:

Price = {D₀ (1 + g_L) + [D₀(H)] (g_S - g_L)} / (k - g_L)

Where:

g_S = Growth rate for initial periods
g_L = Growth rate for final periods (mature growth rate)
D₀ = Initial cash flow used for valuation
k = Investor's required rate of return
H = Years of declining growth (half years)

a) Create a function for the H-model and find a firm's common stock value with:

i. g_S = 17.5%
ii. g_L = .035
iii. D₀ = 2.25
iv.  k = 12.5%
v. H = 10