Case Analysis of Spreadsheet

Executive Summary The problem is to evaluate the enthronization options available straightaway to fund education plans 12 years from now. Six assorted cleaves issued in the year 2008 with different interest rates and adulthood periods atomic number 18 available today (2009). Using this portfolio, a liability of Rs. 30 lakhs for 4 consecutive years (starting from the year 2021) c all for to be met. The interest rate is assumed to be unremarkably distributed with a mean of 7% and a standard difference of 2%. Further, there are 2 different cases. In the first case, the aim is to ensure a maximum 5% obtain of the famine in 2024 exceeding Rs. 15 lakhs (i.e., famine NPV of Rs. 5,42,935) whereas in the second case the aim is to minimize the chance of any dearth with a maximum current investment of Rs. 45 lakhs. It is assumed in the analysis that coupon payments for all bonds are made at the end of the year whereas the liability requirements pertain to the beginning of years 2021-2024. In assign to authorize the problem, first the NPVs, the durations and the convexities of all the bond payments and the liability were calculated (Table 1).

Then, the solver was run to determine a portfolio such that the NPVs, the durations and the convexities of the portfolio and the liability are equal but no feasible solution was instal in this case. Thus, a number of different scenarios were tried whose solutions devour been summarized in Table 2. In scenario 1, only the coldness for twinned durations was relaxed. The solution obtained was to purchase 271.67 units of bond 2 and 240.64 units of bond 3 resulting in equal NPVs for the portfolio and the liability with 17.6% chance of shortfall in 2024 exceeding Rs. 15 lakhs (Figure 1). In scenario 2, only the constraint for matching the convexities was relaxed and the solution obtained was to purchase 544.63 units of bond 3 and 3.94 units of bond 5 resulting in a 19.19% chance of exceeding the allowed shortfall (Figure 2). In scenario 3, the portfolio value... If you want to get a full essay, order it on our website: Ordercustompaper.com

If you want to get a full essay, wisit our page: write my paper