J
Jan Jansens
Hi, i've got a spreadsheet with a function i would like to duplicate:
There are 2 cells, one with the Yield of a security (in this case a
bond) and another cell with the price of the security (the same bond).
Lets say A1 holds the price and cell A2 holds the yield. Now Excel
functions Price() and Yield() use more or less the same arguments and
let you calculate the Price of a bond, based on a certain yield OR let
you calculate the yield of a bond, based on a certain price. So it's a
bit like the chicken and the egg...you need a price to calculate yield
or you need a yield to calculate a price.
In this spreadsheet i'm having they allow you to type in the Price of a
security in cell A1, which will give you a yield in cell a2. The nice
thing however is the following: if you type a yield in cell a2 it will
give you the price in cell a1. So based upon your input Excel looks at
the cell where you have input the value and puts a function in the
other cell....it calculates the result and puts the original function
back into the cell where you have just typed a value, allowing you to
do further calculations with that function....So even when you type a
value in a cell, somehow excel still knows what functions should behind
it once it has done it's calculations......maybe this is not too clear
so here's an example of what i want to do...
A bond has the following terms:
February 15, 1991, settlement date
November 15, 1999, maturity date
5.75 percent semiannual coupon
6.50 percent yield
$100 redemption value
Frequency is semiannual
30/360 basis
Cell A1 = PRICE("2/15/91","11/15/99",0.0575,0.065,100,2,0)
Cell A2 = YIELD("2/15/91","11/15/99",0.0575,95.04287,100,2,0)
Normally 0.065 is the result of cell A2 and 95.04287 is the result of
cell A1.
Now in this spreadsheet i have they let you enter a value and then the
value of cell A2 will change ( so i suppose it most look something like
this in cell A2: =YIELD("2/15/91","11/15/99",0.0575,A1,100,2,0)). But if
you type a value in cell A2 it will also change the value for cell A1
(so that should look like
A1:=PRICE("2/15/91","11/15/99",0.0575,A2,100,2,0)) .
So somehow excel puts the formula back into the cell A1 or A2 after you
enter a value in it......without creating a circular
reference........and while leaving the result of the calculations in
the other cells.....
Really appreciate any suggestions you may have as i'm getting desperate
on this one.......
There are 2 cells, one with the Yield of a security (in this case a
bond) and another cell with the price of the security (the same bond).
Lets say A1 holds the price and cell A2 holds the yield. Now Excel
functions Price() and Yield() use more or less the same arguments and
let you calculate the Price of a bond, based on a certain yield OR let
you calculate the yield of a bond, based on a certain price. So it's a
bit like the chicken and the egg...you need a price to calculate yield
or you need a yield to calculate a price.
In this spreadsheet i'm having they allow you to type in the Price of a
security in cell A1, which will give you a yield in cell a2. The nice
thing however is the following: if you type a yield in cell a2 it will
give you the price in cell a1. So based upon your input Excel looks at
the cell where you have input the value and puts a function in the
other cell....it calculates the result and puts the original function
back into the cell where you have just typed a value, allowing you to
do further calculations with that function....So even when you type a
value in a cell, somehow excel still knows what functions should behind
it once it has done it's calculations......maybe this is not too clear
so here's an example of what i want to do...
A bond has the following terms:
February 15, 1991, settlement date
November 15, 1999, maturity date
5.75 percent semiannual coupon
6.50 percent yield
$100 redemption value
Frequency is semiannual
30/360 basis
Cell A1 = PRICE("2/15/91","11/15/99",0.0575,0.065,100,2,0)
Cell A2 = YIELD("2/15/91","11/15/99",0.0575,95.04287,100,2,0)
Normally 0.065 is the result of cell A2 and 95.04287 is the result of
cell A1.
Now in this spreadsheet i have they let you enter a value and then the
value of cell A2 will change ( so i suppose it most look something like
this in cell A2: =YIELD("2/15/91","11/15/99",0.0575,A1,100,2,0)). But if
you type a value in cell A2 it will also change the value for cell A1
(so that should look like
A1:=PRICE("2/15/91","11/15/99",0.0575,A2,100,2,0)) .
So somehow excel puts the formula back into the cell A1 or A2 after you
enter a value in it......without creating a circular
reference........and while leaving the result of the calculations in
the other cells.....
Really appreciate any suggestions you may have as i'm getting desperate
on this one.......