Bond Valuation Spreadsheet

In: Business and Management

Submitted By edinero
Words 573
Pages 3
Week 3 DQ 2 Forecasting Methods

Read Problem 6 in Chapter 6 of your textbook. Calculate and answer parts a through d. Include all calculations and spreadsheets in your post. Explain why the moving average method was used instead of another forecasting method. What might be another forecasting method that could prove to be just as useful? Your initial post should be 200-250 words.

Below see the number of mergers that took place over a 12-year period in the savings and loan industry.

|Year | |Mergers | |Year |
|2005 |61 |52.6 |8.4 |70.56 |
|2006 |83 |55.6 |27.4 |750.76 |
|2007 |123 |63 |60 |3,600 |
|2008 |97 |75.2 |21.8 |475.24 |
|2009 |186 |85.6 |100.4 |10,080.16 |
|2010 |225 |110 |115 |13,225 |
|2011 |240 |142.8 |97.2 |9,447.84 |
|Total | | |430.2 |37,649.56 |

MSE = 37,649.56 / 7 = 5,378.51

MAD = 430.2 / 7 = 61.46

c. The 5-year weighted moving average is used to forecast the number of mergers for 2012. Using…...

Similar Documents


...Spreadsheet Analysis & Modeling MIS-505 Questions for Final Exam 1. What is name range? How can ranges be named? What are its uses? 2. What is lookup in Excel? What are the different kinds of lookup functions? What are its uses? 3. What is Index function? Write down the procedure of index function. What are its uses? 4. What is Match function? How you can write an Index and Match functions in a nested way? What are its uses? 5. Describe different kinds of text functions in Excel (such as left, right, mid). 6. How can you put date in Excel? What are its uses? 7. What is NETWORKINGDAYS function in Excel? What is NETWORKINGDAYS.INT function in Excel? What are its uses? 8. What is workday and WORKDAY.INT functions in Excel? What are its uses? 9. What is Net Present Value? How do NPV and XNPV function work in Excel? What are its uses? 10. What is internal rate of return (IRR)? How do IRR, XIRR, and MIRR work in Excel? What are its uses? 11. What is present value? How does PV function work? What are its uses? 12. What is Future Value? How does FV function work? What are its uses? 13. Write down the functions of PMT, PPMT, IPMT. How can you use these functions to prepare a loan amortization schedule? 14. Write down the function of IF Statement, AND, OR and XOR. 15. Describe different Time functions. What are its uses? 16. What is What If Analysis? Describe different kind of what if analysis. What are its uses...

Words: 474 - Pages: 2

Bond Valuation

...BOND VALUATION Bond Bond is a long term contract under which a borrower agrees to make payments of interest and principal, on specific dates, to the holders of the bond Key characteristics: VB = value of a bond/bond price M = par or maturity value of the bond; it is the stated face value of the bond and this is amount that must be paid off at maturity and it is often equal to $ 1.000 INT = coupon payment or dollars of interest paid each year; (Coupon rate x Par value) rk = coupon interest rate; (coupon payment / par value) rd = the bond's required rate of the return; that is the market rate of interest for that type of bond; it is also called the yield N = number of years before the bond matures; maturity date is a date on which the par value must be repaid m = number of discounting periods per year The value of any financial asset - a stock, a bond, a lease, or even a physical asset such as an apartment building is simply the present value of the cash flows the asset is expected to produce. Bond Valuation The cash flows from a specific bond depend on the contractual features meaning the type of the bond. The following general equation, written in several forms, can be used to find the value of any bond, VB. = (1 + ) + (1 + ) + ⋯+ ) (1 + + ) + (1 + ∙ ) = (1 + So, the cash flows consist of an annuity of N years plus a lump sum payment at the end of Year N. 1. Standard coupon-bearing bond Standard coupon-bearing bond =the cash flows consist of interest payment......

Words: 3870 - Pages: 16

Valuation of Bonds

...modeling approach of evaluating accounting and non accounting data over an extended time period for the sole purpose of understanding preemptive trends on firms that have been successful and those that have failed. INTRODUCTION We understand that financial accounting’s main goal is to provide its intended audience with the necessary information that will allow for a more informed investment decision. Corporate bankruptcy is of serious consideration due to its huge economic and social cost. Regardless of the growing concern over how useful accounting information is when predicting future financial performance, studies continue to support the fact that financial ratios still are a good indicator of future performance. Much accounting based valuation has focused on analyzing historical and forecasted accounting numbers. There is a difficulty in accurately predicting long term future performance since specific risk factors that have an impact on success may not be discovered. Whereas, evaluating in short term periods, you will be able to identify more interrelated patterns within the ratios. Researchers should focus on financial ratios that drive the firm; these involve profitability (ROA) since capital markets are concerned about the ability of firms to repay debts, cash flow, since this is a measure of the firm’s ability to use cash from operations to service principal and interest payments, and lastly, leverage. This is a measure of the debt to be repaid relative to total......

Words: 2240 - Pages: 9


...Bonds are appealing to investors because they provide a generous amount of current income and they can often generate large capital gains. These two sources of income together can lead to attractive and highly competitive investor returns. Bonds make an attractive investment outlet because of their versatility. They can provide a conservative investor with high current income or they can be used aggressively by investors who prefer capital gains. Given the wide and frequent swings in interest rates, investors can find a variety of investment opportunities. In addition to their versatility, certain types of bonds can be used to shelter income from taxes. While municipal bonds are perhaps the best known tax shelters, some Treasury and federal agency bonds also give investors some tax advantages. Bonds are exposed to the following five major types of risk: (1) Interest rate risk: This affects the market as a whole and therefore translates into market risk. When market interest rates rise, bond prices fall, and vice versa. (2) Purchasing power risk: This is the risk caused by inflation. When inflation heats up, bond yields lag behind inflation rates. A bond investor is locked into a fixed-coupon bond even though market yields are rising with inflation. (3) Business/financial risk: This refers to the risk that the issuer will default on interest and/or principal payments. Business risk is related to the quality and integrity of the issuer, whereas financial risk relates to the......

Words: 15669 - Pages: 63

Bond Valuation

...Bond Valuation By Anuj Joshi Note 1 Bond Valuation Fixed income paying securities. 1. Theoretical price or value of bond depends upon. i. Coupon Payment : Fixed amount of interest to be received after prescribed frequency. ii. Maturity Value [Unless otherwise given is exam, we should take face value] iii. Discount Rate : It should always be market interest rate 2. What is market interest rate Market interest rate is derived from comparable listed bond. The comparison is based on risk and life of the bond. E.g. If we are valuing a bond which is unlisted and have 5 years of life, then we should look for a bond which is similar in risk profile (i.e. same credit rating)and having similar life. The YTM (Yield to Maturity) of listed bond is called market interest rate The YTM of a bond is nothing but IRR of the bond. 3. Value of a bond = PV of Coupon Amount + PV of Maturity Value [Remember CF and discount rate are before tax] Concept Point: i. Coupon rate is a historical rate and should never be used as a discount rate. In exam, if no other information is available, then only we should assume coupon rate of interest as market rate of interest. ii. Remember, Cost of Capital or Discount Rate is a future concept and it represents opportunity cost on the date of valuation. iii. YTM of a similar bond (i.e. current market interest rate) is the appropriate discount rate for bond valuation. How to value a bond which pays interest at a frequency lower than annually (e...

Words: 2748 - Pages: 11

Bond Valuation

...1.0 Introduction Bond valuation is the determination of the fair price of a bond. As with any security or capital investment, the theoretical fair value of a bond is the present value of the stream of cash flows it is expected to generate. This essay is about different types of bonds and the instruction of the riskiness of bonds. Firstly, this essay will make a general overview of the types of bonds. Subsequently it will discuss the types of risks to which both bond investors and issuers are exposed. Finally it will make an analysis of the bond markets. 2.0 Basic information of bonds Bond is a form of long-term debt instrument. For example, a contractual liability, basically just a certificate showing that a borrower promises to repay interest and principal, on specific dates, to the holders of the bond. Bonds are one of the most important types of securities. There is a wide variety of these securities. It may seem confusing, but in actuality just a few characteristics distinguish the various types of bonds. 3.0 Various Types of Bonds Bonds are issued by both governments and corporations. Bonds are issued by public authorities, credit institutions, companies and supranational institutions in the primary markets. The most common process for issuing bonds is through underwriting. When a bond issue is underwritten, one or more securities firms or banks, forming a syndicate, buy the entire issue of bonds from the issuer and re-sell them to investors. The security......

Words: 434 - Pages: 2

Spreadsheet Modeling

... SPREADSHEET MODELING IN CORPORATE FINANCE To accompany Principles of Corporate Finance by Brealey and Myers CRAIG W. HOLDEN Richard G. Brinkman Faculty Fellow and Associate Professor Kelley School of Business Indiana University Prentice Hall, Upper Saddle River, New Jersey 07458 To Kathryn, you’re the inspiration, and to Diana and Jimmy, with joy and pride. Craig CONTENTS Preface PART 1 TIME VALUE OF MONEY Chapter 1 Single Cash Flow 1.1 Present Value 1.2 Future Value Problems Chapter 2 Annuity 2.1 Present Value 2.2 Future Value 2.3 System of Four Annuity Variables Problems Chapter 3 Net Present Value 3.1 Constant Discount Rate 3.2 General Discount Rate Problems Chapter 4 Real and Inflation 4.1 Constant Discount Rate 4.2 General Discount Rate Problems Chapter 5 Loan Amortization 5.1 Basics 5.2 Sensitivity Analysis Problems PART 2 VALUATION Chapter 6 Bond Valuation 6.1 Basics 6.2 By Yield To Maturity 6.3 System Of Five Bond Variables 6.4 Dynamic Chart Problems Chapter 7 Stock Valuation 7.1 Two Stage 7.2 Dynamic Chart Problems Chapter 8 The Yield Curve 8.1 Obtaining It From Bond Listings 8.2 Using It To Price A Coupon Bond 8.3 Using It To Determine Forward Rates Problems Chapter 9 U.S. Yield Curve Dynamics 9.1 Dynamic Chart Problems PART 3 CAPITAL BUDGETING Chapter 10 Project NPV 10.1 Basics 10.2 Forecasting Cash Flows 10.3 Working Capital 10.4 Sensitivity Analysis Problems Chapter 11 Cost-Reducing......

Words: 49278 - Pages: 198


...Strengths and Weaknesses of Each Spreadsheet (Teacher) Aims to help the teacher: * Recording Results * Tracking the Learners’ Progress Strengths: One of the strengths of the teacher’s spreadsheet was the use of conditional formatting. Conditional formatting was helpful by easily presenting data, by formatting a range of cells depending on certain grades or scores means that analysing and categorising data will much easier with a visual representation and the teacher can make more accurate conclusions. The way the tables are laid out, and by defining a range is very useful for a teacher when it comes to neatly organising a spreadsheet with many tables of class’s results. By defining a range means that tables are easy to input into formulae, and will help reduce errors. AutoSum is very beneficial to a teacher because it simply adds up an entire column without having to add each cell individually. This saves a lot of time, and helps reduce errors, especially if there is lots of students’ data. Weaknesses: Macros are not included in the spreadsheet, which would’ve been very useful for a teacher. Macros stop repetitive tasks being completed over and over again. This is useful as it saves a lot of time to the teacher, e.g. if they click a customised button which filters data or changes sheets. This will also improve the usability of the spreadsheet and make the data input easier. Goal seek, which also isn’t included in the spreadsheet, would also be very......

Words: 1576 - Pages: 7

Bond Valuation

...Financial Management  Unit 4  Unit 4  4.1  Introduction  4.2  Valuation of Bonds  Types of Bonds  4.2.1  Irredeemable or Perpetual Bonds  Valuation Of Bonds And Shares  4.2.2  Redeemable or Bonds with Maturity Period  4.2.3  Zero Coupon Bond  Bond­yield Measures  4.2.1  Holding Period Rate of Return  4.2.2  Current Yield  4.2.3  Yield to Maturity (YTM)  4.2.4  Bond Value Theorems  4.3  Valuation of Shares  4.3.1  Valuation of Preference Shares  4.3.2  Valuation of Ordinary Shares  4.4  Summary  Solved Problems  Terminal Questions  Answers to SAQs and TQs  4.1  Introduction  Valuation is the process of linking risk with returns to determine the worth of an asset. Assets can be  real or financial; securities are called financial assets, physical assets are real assets. The ultimate  goal  of  any  individual  investor  is  maximization  of  profits.  Investment  management  is  a  continuous  process requiring constant monitoring. The value of an asset depends on the cash flow it is expected  to provide  over  the  holding period.  The fact  that as  on date  there  is no  method  by  which  prices  of  shares  and  bonds  can  be  accurately  predicted  should  be  kept  in  mind  by  an  investor  before  he  decides  to  take  an  investment  decision.  The  present  chapter  will  help  us  to  know  why  some Sikkim Manipal University  50  Financial Management  Unit 4  securities  are  priced  higher  than  others. We  can  design ......

Words: 3016 - Pages: 13


...their own management or wealthy raiders, who saw potential value in restructuring or breaking up these firms. In the 1990s, we saw a wave of consolidation in the media business as telecommunications firms acquired entertainment firms, and entertainment firms acquired cable businesses. Through time, firms have also acquired or merged with other firms to gain the benefits of synergy, in the form of either higher growth, as in the Disney acquisition of Capital Cities, or lower costs. Acquisitions seem to offer firms a short cut to their strategic objectives, but the process has its costs. In this chapter, we examine the four basic steps in an acquisition, starting with establishing an acquisition motive, continuing with the identification and valuation of a target firm, and following up with structuring and paying for the deal. The final, and often the most difficult, step is making the acquisition work after the deal is consummated. Background on Acquisitions When we talk about acquisitions or takeovers, we are talking about a number of different transactions. These transactions can range from one firm merging with another 1 2 firm to create a new firm to managers of a firm acquiring the firm from its stockholders and creating a private firm. We begin this section by looking at the different forms taken by acquisitions, continue the section by providing an overview on the acquisition process and conclude by examining the history of the acquisitions in the United......

Words: 21338 - Pages: 86

Bond Valuation

...BondBond Valuation Valuing the cash flows Chapter 7 (1) coupon payment (interest payment) = (coupon rate * principal) Bonds, Bond Valuation, and Interest Rates usually paid every 6 months (2) maturity value = principal or par value = $1000 Example (coupon rate = rd) Five year corp. bond pay coupons at 10% rate, market rate (discount rate) (required rate of return) is 10% Example (coupon rate = rd) Define Terms C rd = 10% 0 1 2 3 4 5 ├───────┼───────┼───────┼───────┼───────┤ P0 $100 $100 $100 $100 $100 $1,000 F n rd P0 = coupon payment = coupon rate x $1000 = 10% x $1,000 = $100 = face amount or maturity value = $1000 = payments to maturity = 5 = required rate of return = 10% = bond value = ? 1 Example (coupon rate = rd) P0 = PV of coupon annuity + PV of lump sum maturity value PV of coupon annuity = $379.08 PV of lump sum maturity value = $620.92 P0 = $379.08 + $620.92 = $1,000.00 Solve with Calculator PMT = C FV = F I/Y = rd N P0 PMT = C FV = F I/Y = rd N = 100 = 1000 = 10% =5 = PV = 1,000 In this case coupon = rd so P0 = F This Bonds sells at PAR Example (coupon rate > rd) Five year corp. bond pay coupons at 10% rate, market rate is 8% = 100 = 1000 = 10% =5 Example (coupon rate > rd) P0 = 399.27 + 680.58 = $1,079.85 Calculator: PMT = C FV = F I/Y = rd N P0 = 100 = 1000 = 8% =5 = PV = $1,079.85 2 Example (coupon rate...

Words: 1436 - Pages: 6

Time Value of Money and Bond Valuation

...which time value of money can help corporate managers in general. The time valuation of money is the idea that money available now could be worth more than the same amount in the future, because that current money has the possibility of earning money in the future. Think if the expression, “It takes money to make money!” If you are guaranteed to have $100.00 now or $100.00 in 3 years, you would probably take the money now. However, the $100.00 you could have now can be utilized to make even more money in the future through investing. This concept is very important in the business world as corporations are always looking to increase investing opportunities that will prove profitable. Time valuation enables corporate managers to determine two major aspects of investments; How much to invest and the rate of return on that investment. A company needs to know how much they need for an initial investment and how much that investment will yield over a given period of time. This is also where compounded interest plays a major role, the more the interest is compounded the greater the yield. Examine the pros and cons of a sinking fund from the viewpoint of both a firm and its bondholders. Determine the fundamental manner in which this knowledge could be helpful to a financial manager. Provide a rationale for your response. Sinking funds are a method of repaying funds that were borrowed via a bond. A bond in simple terms is an IOU to individual persons or corporations that......

Words: 622 - Pages: 3

Bond Valuation in Bd

...ICB Mutual Funds ICB Mutual Funds are also known as close ended Mutual Funds. The issued capital of a Mutual Fund is limited, that is, a Mutual Fund offers a limited number of certificates for sale to the public. The amount of capital and the number of certificates of each Mutual Fund remains unchanged. ICB Mutual Funds are independent of one another. A Mutual Fund being listed is traded on the Stock Exchanges. Price of Mutual Fund certificates after IPO is determined on the Stock Exchanges through interaction of supply and demand. The market price of a Mutual Fund certificates is available in Stock exchange quotations and in newspapers. Performances of ICB Mutual Fund The Considerations underlying the performance evaluation of mutual funds is a matter of concern to the fund managers, investors and researchers alike. The present paper attempts to answer two questions relating to mutual fund performance; 1. Whether the growth oriented Mutual Fund are earning higher returns than the benchmark returns (or market Portfolio/Index returns) in terms of risk. 2. Whether the growth oriented mutual funds are offering the advantages of Diversification, Market timing and Selectivity of Securities to their investors. This paper attempts to answer the questions raised, by initially describing some basic concepts and later by employing a methodology which was used by Sharpe (1966), Treynor (1965), and Jenson (1968) and finally given appropriate comments. ...

Words: 296 - Pages: 2

Bond Valuation

...Bond Valuation: * How do we use NPV to value bonds? One simply computes the present value of the cash flows at the appropriate rate of return. This corresponds approximately to the full price of the bond (as opposed to the listed price).   * E.g.: a one period, $1000 bond, 10% coupon is valued at: $1037 (1100/1.06) if the market rate of return is 6%. The bond sells at a premium.   * $1000 if the market rate of return is 10%. The bond sells at par.   * $982 if the market rate of return is 12%. The bond sells at a discount. Tentative Conclusions * The higher the appropriate interest rate, the lower the price of the bond.  * If the yield matches the coupon , then the bond sells at par. * If the yield is higher than the coupon, the bond sells at a discount. * If the yield is lower than the coupon, the bond sells at a premium Example: * Consider now an infinite bond, paying a 10% coupon, i.e. $100 forever.   * Then if the market return is 10% the bond sells at 100/0.1 =1000. * If the market return is 6% the bond sells at 100/0.06 = 1667 * If the market return is 12% the bond sells at 100/0.12 = 833 * A tentative conclusion: it seems that longer maturity bonds are affected more by interest rate swings. * We will modify this conclusion later. Valuing a Bond * If today is October 1, 2010, what is the value of the following bond? An IBM Bond pays $115 every September 30 for 5 years. In September 2015 it......

Words: 1065 - Pages: 5


...FV TVM - INVESTING CONTD. SOLUTIONS STEP 2: WE ARE SOLVING FOR I/Y P/Y =12 NOTE: since you are saving MONTHLY, you must set P/Y to 12 N = 15 X 12 = 180 PV = -100000 PMT = -500 FV = 1500000 SOLVE FOR I/Y = 16.20% 12. USING BOND SPREADSHEET (covered later in course). You want to buy an A rated bond that matures in 15 years. The coupon rate is 8%. The yield on A rated bonds in the same maturity range is 7.5%. What price would you pay for this bond? Corporate bonds mature at PAR. Par = $1000 • Corporate bonds pay interest coupons SEMIANNUALLY. P/Y = 2 • The stated interest rate on the bond is fixed for the life of the bond. This is called the “Coupon rate.” • All bonds are priced to the market yield on bonds of similar type, quality and maturity. This yield is always changing and bonds adjust to it by the price fluctuating. If yields in the market go UP, bond prices go DOWN. SET BGN SET P/Y = 2 N = 30 (Remember N = P/Y times the number of years) I/Y = 7.5 In a bond problem I/Y is the yield on other similar bonds. DO NOT use the coupon rate on the bond. FV = 1000 All bonds mature at par. Par = 1000 PMT = 40 Bonds pay interest semiannually. This bond has a coupon rate of 8%. Annual interest = 8% x $1000 = $80 Each coupon is therefore half the annual interest of $80 or $40 SOLVE FOR PV 1071.32 PROBLEM A loan of $50,000 is due 10 years from today. The...

Words: 1716 - Pages: 7