FIN205: The First Department you Joined was Relationship Management (RM) Singapore Team: Data Technologies for Financial Modelling Assignment, SUSS, Singapore

Question 1

The first department you joined was Relationship Management (RM) Singapore Team. A relationship manager assistant approached you. Her name is Veronica. She just joined the bank recently. She understands Microsoft Excel spreadsheet well but has little knowledge about financial instruments and their characteristics. She realised that you are studying at SUSS and are a subject matter expert in Financial Modelling.

She hands an investment portfolio (in Singapore Dollar, SGD) to you and seeks your help to identify the category of each financial asset and have their characteristics explained. She also needs the current value of each financial asset so that she can report to her manager later.

Veronica’s client data:

After careful study and analysis, you will be writing her an email with Microsoft Excel spreadsheet screenshots. Your email should contain the following details:

• Hypothesise and categorise the above-mentioned financial assets, whether the asset is a Stock (with forecasted dividends), Floating Rate Notes, Fixed Deposit, Zero Coupon Bond or Fixed Rate Bond. After categorising, provide descriptions on the feature of each asset class/category. Your logic and reasoning should be presented in less than 250 words.
• Solve for the present value for each financial asset using the relevant Microsoft Excel function. Show your use of function with the inputs (e.g., =PV($A$1, $B$1,,C1). You may use the FORMULATEXT() function in Excel). All answers should be expressed in the relevant currency and round-off to two (2) decimal places.
• To help Veronica to convince her manager of the accuracy of the computation, verify all the answers in Question 1(b) using the formula approach and show your formula with the inputs. All answers should be expressed in the relevant currency and round-off to two (2) decimal places.

Question 2

Veronica needed to price three (3) Singapore dollar-denominated bonds. She emailed you and asked for help in term of valuation as well as seeking your expertise to clear some of her doubts which were:

(i). A higher coupon yield for 20-year bond as compared to that of 2-year bond from the same bond issuer, SGS.

(ii). A wide coupon yield gap between 20-year SGS and 20-year ABC’s bonds.

Attached within her emails are the term sheets for the three bonds.

1.  2-year Singapore Government Securities (SGS) bond Standard & Poor’s AAA-rated Coupon rate 3.125% per annum Quarterly coupon payment Par value is $100

2. 20-year Singapore Government Securities (SGS) bond Standard & Poor’s AAA-rated Coupon rate 4.1% per annum Semi-annual coupon payment Par value is $100

3. 20-year Company ABC bond Standard & Poor’s BBB rated Coupon rate 6% per annum Annual coupon payment Par value is $100 Create a Microsoft Excel spreadsheet capturing the bond information above and do the following:

A. Calculate the present value for each bond using the appropriate Microsoft Excel function. In your spreadsheet, you may create an additional cell (where necessary) to help your calculation. Show your use of the function with the inputs (you may use the FORMULATEXT() function in Excel). All answers should be expressed in the relevant currency and round-off to two (2) decimal place.

B. Illustrate the purpose of SGS bonds and provide answers to Veronica’s concern on: “Why there is a higher coupon yield for 20-year bond as compared to that of 2-year bond from the same bond issuer SGS”. Explain your rationale, in not more than 100 words.

C. Explain and provide answers to Veronica’s concern on: “Why there is a wide coupon yield gap between 20-year SGS and 20-year ABC’s bonds”. Explain your rationale, in not more than 100 words.

Question 3

Two weeks later, you are assigned to the Quant team and you are asked by the team leader to generate an SGD discount factor table for 5 years. The cost of borrowing has increased to 3% yet the cost of borrowing curve continued to stay flat.

A. Solve for the discount factors using the formula approach. Show your use of the formula with the inputs (you may use the FORMULATEXT() function in Excel). All answers should be rounded-off to four (4) decimal places.

B. Upon calculation, plot a graph to show the shape of the curve, using a discount factor as y-axis and tenor as the x-axis. Label your graph.

C. You are given a structured product to price. The structured product is a collection or combination of these two financial assets: Asset alpha and Asset omega, with predicted future cash flows as shown below.

With the Discount Factor Table, you created in Question 3(a), determine the price for the structured product. The final answer should be expressed up to two (2) decimal places.

D. “Discounted Cashflow Method can be utilised to price any financial instrument.”

Are you for or against this statement? Explain your rationale with examples, in not more than 100 words.

Question 4

On the last day of your internship, your boss organised a farewell party for you. At the party, you met a Financial Analyst, a Relationship Manager and an FX Trader.

The conversations circled around these areas:

(i). The Financial Analyst realised that you are studying Financial Modelling in SUSS. He mentioned that he often used financial models as a tool to make a decision.

(ii). The Relationship Manager also mentioned that she often used financial models as a tool to help her prospects and existing clients to perform personal risk analysis and financial planning.

(iii). The FX Trader, however, mentioned that he never used any financial model or formula but merely depend on financial news, technical analysis and charting for his FX Trading.

a. The boss expects you to write him a report of not more than 500 words. In the report, examine what you have learned during your internship by listing at least five (5) observations as operating rhythm at the Relationship Management and Quant team, how you have applied your financial modelling knowledge and etc.

b. In addition, in the report, include your opinions (with at least two (2) supportive arguments) on whether you are agreeable with:

(i). the financial analyst, on the features of a good financial model that allow it to be used as a tool for making a sound investment decision;

(ii). the relationship manager, on which aspects of financial modelling could assist in the process of financial planning;

(iii). the FX trader, on the relevancy of financial modelling in trading.