Equity & Fixed Income Securities

I was wondering if someone could help me with questions 3 and 4? I keep running formula’s but I the answers I’m getting just can’t be right.. Please review the ‘overview’ tab. I need the fx function formulas for PV, FV and RATE for sure just regular formulas for the rest. Thanks so much!

Input boxes in tan
Output boxes in yellow
Given data in blue
Calculations in red
Answers in green
NOTE: Some functions used in these spreadsheets may require that
the “Analysis ToolPak” or “Solver Add-In” be installed in Excel.
To install these, click on the Office button
then “Excel Options,” “Add-Ins” and select
“Go.” Check “Analyis ToolPak” and
“Solver Add-In,” then click “OK.”
The concept of time value of
money and bonds go hand in hand.
In these examples, you will be able
to solve time value of money
problems with Excel.
These components are required
for each problem that you solve.
To begin a function, start with the
fx on your toolbar
Once you select fx
on your tool bar,
search for the PV
function
PV
FV
PMT
N
I
Once you select
on your tool bar,
search for the
function
ce you select fx
your tool bar,
arch for the PV
Once you select fx
on your tool bar,
search for the FV
function
Enter any payment amount.
Usually 0, or the coupon
payment if calculating bond
prices.
Once you select fx
on your tool bar,
search for the RATE
function
Make sure you
calculate the correct
number of
payments.
Sample problem
Present Value
Output area:
Input area:
Present value
($505.07)
($4,688.96)
($12,708.73)
($1,009.46)
Years
Interest rate Future value
15
5% $
1,050
25
4% $
12,500
10
7% $
25,000
20
2% $
1,500
Future Value
Output area:
Input area:
Future value
$2,182.87
$33,322.95
$49,178.78
$2,228.92
Years
Interest rate Present value
15
5% $
(1,050)
25
4% $
(12,500)
10
7% $
(25,000)
20
2% $
(1,500)
Interest Rate
Output area:
Input area:
Interest Rate
5%
4%
7%
2%
Years Future Value Present value
15
2,183 $
(1,050)
25
33,323 $
(12,500)
10
49,179 $
(25,000)
20
2,229 $
(1,500)
The present value will always come
out negative (red or in parenthesis) in
Excel. Think of it as you are taking the
amount out of your pocket right now,
so you would be in the negative!
1.
PV
FV
PMT
N
I
2.
The Piedmont Flyers have issued a 30-year bond with a face value of $100
$751.82
($1,000)
-50
30
7%
The Piedmont Flyers have issued a 30-year bond with a face value of $1000
PV
$723.24
FV
($1,000)
PMT
-20
N
60
I
3%
3.
The Piedmont Flyers have issued a 8-year bond with a face value of $1000.
PV
FV
PMT
N
I
4.
PV
FV
PMT
N
I
The Piedmont Flyers have issued a 30-year bond with a selling price of $75
ith a face value of $1000 and a 5% coupon rate. The market is yielding 7%, what is the current selling price
th a face value of $1000 that pays semiannually with a 4% coupon rate. The market is yielding 6%, what is
h a face value of $1000. The price of the bond is $880. The market rate is 10% annually. What is the coupon
th a selling price of $750 and a coupon payment of $100. The face value is $1000. What is the return on th
is yielding 6%, what is the current selling price of the bond?