• We are pleased to announce that the winner of our Feedback Prize Draw for the Winter 2024-25 session and winning £150 of gift vouchers is Zhao Liang Tay. Congratulations to Zhao Liang. If you fancy winning £150 worth of gift vouchers (from a major UK store) for the Summer 2025 exam sitting for just a few minutes of your time throughout the session, please see our website at https://www.acted.co.uk/further-info.html?pat=feedback#feedback-prize for more information on how you can make sure your name is included in the draw at the end of the session.
  • Please be advised that the SP1, SP5 and SP7 X1 deadline is the 14th July and not the 17th June as first stated. Please accept out apologies for any confusion caused.

CM1B Sep 2020 Excel Solutions

S

Syukri R

Member
Hi. On the question 2(ii). I'm finding it difficult to understand where the money and real yield rate come from? I've been trying to solve it manually but still couldn't get the answers (money yield: 4.518% and real yield: 1.249%). What kind of formula should I use?

Thanks
 
These two answers have been found using the GoalSeek functionality in Excel. GoalSeek is Excel's equivalent to finding a value using trial and error. You can access GoalSeek on the 'Data' tab in Excel, and then go to 'What If Analysis'.

GoalSeek works by changing one value (here the interest rate) until another cell (here the total present value) takes a specified value. So we set up the GoalSeek to set the cell containing the total present value equal to 10,000,000, by changing the cell containing the yield.

You can spot those cells that have been worked out using GoalSeek by clicking on them - in the formula bar there will be masses of decimals (as Excel loves accuracy).

If you are unfamiliar with Goalseek, there are examples of using it in the CM1 Paper B Online Resources - including in the videos for Section 4, Q4 'Simple and effective rates of interest' and Section 10, Q2 'Loan schedule, increasing repayments'.
 
You must log in or register to reply here.
Back
Top