# Solved Excel Function for a Bond Table

February 7, 2013 at 14:22:04
Specs: Windows 7
 I need to write a function that will number a set of cells, counting up from period one to a specified point (period x). The final number,i.e. the total number of periods, is calculated in the assumption table.I am thinking this will either require a nested IF statement, or some type of V-LOOKUP, but the trouble is getting the function to count instead of just displaying the final value or getting an error.I also need the formatting of the table to move with the number of periods, so I don't have a bunch of formatted but empty cells.Any ideas on how to get this function to work would be greatly appreciated.

See More: Excel Function for a Bond Table

#1
February 7, 2013 at 15:05:20

Report •

#2
February 7, 2013 at 15:27:53
 I am trying to calculate the number of periods to maturity on a bond. I have set up an assumption table that allows the user to input number of years, annual or semi annual compounding, the par value of the bond and the coupon payment and interest rate. I now need set up a chart that will be able to track the life of the bond from the 1 period until the last period. the first period will always be 1, but the last period depends on the number of years the user inputs multiplied by the number of times the interest is compounded per year (either 1 or 2).This means that in the column I have labeled 'Periods', I need to write a function that will count up from 1 to the result of the 'Years" cell multiplied by the 'Compounding Periods' cell.As far as the formatting, What I mean is that if I have one row highlighted blue, and the other highlighted red (to make it look like a typical table), I want this formatting to only be visible for the cells that contain data. If the user only inputs 5 years, compounded annually, there will only be 5 periods and therefore only 5 rows. My question with regard to this is will the function that I write also allow me to format the cells that have data in them, and copy this format down when more cells contain data?

Report •

#3
February 7, 2013 at 17:56:24
 Since you didn't supply any Row or Column information, the best I can do is offer a generic solution which you can modify to fit your needs.For the "function" try something like this:``` A B C D 1 YEARS COMPOUND # PERIODS PERIODS 2 5 2 =A2*B2 =IF(ROW()-1<=\$C\$2,ROW()-1,"") ```Drag the formula in D2 down as far as the maximum number of periods you might ever need. In the example above, you should see the numbers 1 - 10 in D2:D11.For the formatting use Conditional Formatting.Select the entire table from D2 down as far as it will ever go and out as many columns as it will extend.1 - Click Conditional Formatting.2 - Click Manage Rules.3 - Click the New Rule Box.4 - Click "Use a formula to determine which cells to format".5 - Paste this in the "Format values where this formula is true:" field.` =AND(\$D2<>"",MOD(ROW(),2)=0)`6 - Click the Format box and chose a Fill color7 - Click OK, Click OK and you should get back to the Rules Manager dialog with your Rule displayed. 8 - Repeat steps 3 - 6 but use this Formula as the New Rule:` =AND(\$D2<>"",MOD(ROW(),2)=1)`9 - Click OK, OK, OK and you should be done.Only the rows with values in Column D will be formatted and the rows will alternate colors based on what color you chose for each Rule.Basically, what is happening is that the MOD(ROW(), 2) = 0 and MOD(ROW(), 2) = 1 determines if the Row number is Odd or Even, filling the Odd rows with one color and the Even rows with the other.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.