Use Excel for the entire assignment. When applicable, you must also use the full financial statement effects template (i.e., no short-hand approach).
Question 2 (Mortgages Payable)
Brian M. owes a mortgage on his primary home. The mortgage terms are as follows:
· Amount due as of 1/1/2013: ?
· Mortgage loan period: 25 years
· Annual interest rate: 4.20%
· Monthly payment amount (starting on 1/31/2013): $1,025.00
Required
· Scenario 1: Using the above information, create a complete amortization table to determine the following information
o Amount due as of 1/1/2013 (use the appropriate TVM function in Excel to determine this)
o Total interest to be paid as of the final payment date
· Scenario 2: Brian M. decides to apply an extra $175.00 per month toward his mortgage. Use the “amount due as of 1/1/2013” calculated in Scenario 1 as the initial mortgage loan balance. Based on above information, create a complete amortization table to determine the following information.
o Total interest to be paid as of the final payment date
o Interest savings relative to Scenario 1 (if any)
o Time savings (i.e., how much sooner the mortgage is repaid) relative to Scenario 1 (if any)
Question 3 (Equity)
Rockies Company goes public on April 10, 20×1 and has a 12/31 fiscal year-end. The Company has 300,000 authorized common shares. Related to going public, the Company has the following transactions in 20×1:
· On April 10th, the Company issues 47,000 common shares at $8.00 per share. The par value of the common shares is $2.00 per share.
· On May 20th, the Company repurchases 7,800 common shares at $11.00 per share.
· On August 10th, the Company declares a $0.30 per share cash dividend for all common shares outstanding (be careful in determining this). The date of record for the dividend is August 28th. The date of payment for the dividend is September 15th.
· On September 30th, the Company reissues 4,800 treasury shares for $13.00 per share.
· On October 10th, the Company declares a $0.30 per share cash dividend for all common shares outstanding (be careful in determining this). The date of record for the dividend is October 28th. The date of payment for the dividend is November 15th.
Required
· Provide the financial statement template effects for all the transactions listed above. Also, include a brief description for memorandum items (i.e., events that occur, but do not result in an accounting transaction) within the template.
· Show how all relevant equity accounts should be formally reported on the Company’s balance sheet as of December 31, 20×1. In other words, show me a “snapshot” of these accounts on the balance sheet. The 20×1 net income for the Company is $105,000 and its January 1, 20×1 balance in retained earnings is $635,000.