Annex 121 Using Excel to calculate efficient frontiers Solver solution

Several commercial providers have developed software that enable one to construct efficient frontiers based on various definitions of risk and with a variety of constraints. But it is helpful to be aware that one of the standard Add-Ins that accompanies Excel, namely Solver, provides a method for calculating efficient portfolios. You first have to check that Solver has been installed with your version of Excel as it is often not installed in a typical installation. You can check by looking in...

Excel Application 172

Suppose that you have available the following 24 monthly returns on an index and that you further wish to calculate the 70 VaR for the quarterly return on a derivative product that provides a return each month equal to the larger of the monthly index return and -10 . Enter the information (including headings) below into range A1 B25 of a spreadsheet. In cell C2, enter the formula max(b2,-10 ). The value in C2 should be 4.0 , or 0.04 (depending on the formatting that you have chosen for the...

The Wilkie Model

The Wilkie model is the name for a family of models, developed and subsequently refined by A.D. Wilkie, starting in 1980. The models were initially developed to determine the reserves needed for particular types of life insurance product, but have subsequently become widely used in the UK and beyond in areas such as pensions consultancy and general investment strategy. The Wilkie model has achieved prominence mainly because it has been published in full (and therefore discussed widely) and was...

The Present Value Of An Annuitycertain

The following types of annuity-certain are particularly important in investment and it is therefore useful to know the general formulae for their present values. 1 A series of payments of one per interval payable in arrears for n intervals The present value of this series of payments is often denoted by the symbol an. Suppose that the rate of interest is i per interval. Then The right-hand side of this equation is the sum of a geometric series with first term equal to 1 1 i and common ratio...