Your client is XYZ company. After discussions it is identified they require a spreadsheet summarising the...

Your client is XYZ company. After discussions it is identified they require a spreadsheet summarising the repayment amounts and current balance for a long-term loan of $500,000 to be repaid over eight years at a rate of 10% per annum. They are interested in business information that will show the amount they need to repay each year.

Show the calculations required for the loan.

Prepare the spreadsheet for the loan. Include a summary section to comment on the table. Round the initial opening balance of the loan up to the nearest dollar and round up to present values in whole dollars.

Expert Solution

The amount of $500000 is to repaid over 10 years in equal instalment.
For this, Present Value Annuity factor of 10% for 10 years is to be calculated, using factor table.
To find the monthly repayable amount, 500000 will be divided y that PV Annuity factor figure.
The same can also be calculated in MS-Excel:

Amount to be repaid over 10 years	$ 500,000
Interest rate	10%
Annual Installment function(formula)	= pmt(10%,10,500000)
Annual repayment (using above MS Excel function) will be	$ 81,372.7

Spreadsheet of repayment as calculated above:

	Beginning Principal Balance	Interest at 10%	Instalment amount	Reduction in Liability	Ending Principal balance
	[A= ‘E’ of Last Year]	[B = A x 10%]	[C= Calculated above]	[D = C – B]	[E = A – D]
Year 1	$ 5,00,000.00	$ 50,000.00	$ 81,372.70	$ 31,372.70	$ 4,68,627.30
Year 2	$ 4,68,627.30	$ 46,862.73	$ 81,372.70	$ 34,509.97	$ 4,34,117.33
Year 3	$ 4,34,117.33	$ 43,411.73	$ 81,372.70	$ 37,960.97	$ 3,96,156.36
Year 4	$ 3,96,156.36	$ 39,615.64	$ 81,372.70	$ 41,757.06	$ 3,54,399.30
Year 5	$ 3,54,399.30	$ 35,439.93	$ 81,372.70	$ 45,932.77	$ 3,08,466.53
Year 6	$ 3,08,466.53	$ 30,846.65	$ 81,372.70	$ 50,526.05	$ 2,57,940.48
Year 7	$ 2,57,940.48	$ 25,794.05	$ 81,372.70	$ 55,578.65	$ 2,02,361.83
Year 8	$ 2,02,361.83	$ 20,236.18	$ 81,372.70	$ 61,136.52	$ 1,41,225.31
Year 9	$ 1,41,225.31	$ 14,122.53	$ 81,372.70	$ 67,250.17	$ 73,975.14
Year 10	$ 73,975.14	$ 7,397.51	$ 81,372.70	$ 73,975.19	$ (0.04)

The above repayment schedule shows that if $81372.70 is repaid for 10 years, whole amount of $500,000 will be repaid along with 10% Interest. Notice that the ending principal balance at the end of Year 10 is ZERO automatically.

ekkarill92 answered 1 year ago

It is the end of the financial year and your client is XYZ company. From the...

It is the end of the financial year and your client is XYZ company. From the following file note summary prepare an evaluation report. File note summary XYZ company. Financial potential $33,000 annually for the previous three years. Accumulated surplus $100,000. Statutory requirements: prepare and lodge BAS and IT. Compilation assurance. Reports provided by XYZ’s CEO. Preference to invest in government bonds. No contingency cash expected.

Your client contact is the finance officer of XYZ company. They phone you briefly on 1.10.20XX...

Your client contact is the finance officer of XYZ company. They phone you briefly on 1.10.20XX and say that the financial position detail report contains too much detail. They also state that there is no explanation for the changes in financial position even though they know that several new desks were purchased in the previous quarter because they recorded them on the asset register. Prepare a file note to show how the feedback will be incorporated into services to the...

I have a client: XYZ company. XYZ makes bicycles in the high side of the market,...

I have a client: XYZ company. XYZ makes bicycles in the high side of the market, $2,500 and above. In many ways they are used by the top riders in competitive races in the World. The variable cost to manufacturer is $1,000. The fixed cost are $500 per bicycle. Profit is $1,000. The manufacturing of the bicycles are in the same plant as the manufacturer of a very plain generic bicycle sold through COSTCO. The plain bicycle sells for $250...

You work for ABC financial management. Your client is XYZ company. The CEO contacts you directly...

You work for ABC financial management. Your client is XYZ company. The CEO contacts you directly on the 1st July, thanking you for the satisfaction of current objectives in line with the financial plan. Current objectives are reporting annual financial reports including preparing the BAS. The client asks that this now ceases as the finance officer will take over the duties. The client asks that you prepare a manual to assist the finance officer in their new duties. The client...

A client at a software design company wants assistance processing their documents. After interviewing the client...

A client at a software design company wants assistance processing their documents. After interviewing the client you find that their documents consist of a one sentence title, a date, and a series of paragraphs. Each paragraph is a simple list of sentences. Paragraphs are separated by new lines. Each document also has an author (first and last name) and a document access level (open, internal or confidential). At a minimal level the client would like the software to be able...

Instructions: After you have completed the calculations on your spreadsheet that correlates to the questions below,...

Instructions: After you have completed the calculations on your spreadsheet that correlates to the questions below, you will copy and paste the portion of that spreadsheet into the Word document for each of the questions. Be sure to also provide a sentence that explains the solution. To save for her newborn son ’s college education, Lea Wilson will invest $1,000 at the end of each year for the next 20 years. The interest rate is 10%. What is the future...

After you've identified five homologs of your favorite gene in your dog, you are eager to...

After you've identified five homologs of your favorite gene in your dog, you are eager to clone them. You download the dog genome sequence and design and order primers for all five homologs. After the first PCR reaction you realize that now your are having issues with non-specific amplification. Which of the following adjustments to the PCR reaction could you do to increase the specificity of the reaction? (choose all that apply) and why 2 sentence Raise the temperature of...

Two annuities are available for purchase that your client has identified. The first annuity pays $2,400...

Two annuities are available for purchase that your client has identified. The first annuity pays $2,400 each month over a 4 year period at a nominal rate of 11% p.a. The second annuity pays $15,000 each six-month period, again over 4 years, at a nominal rate of 12% p.a., but has an annual fee of $500, paid at the beginning of each year. Identify which of the two annuities would be a better option for your client.

Two annuities are available for purchase that your client has identified. The first annuity pays $7,000...

Two annuities are available for purchase that your client has identified. The first annuity pays $7,000 each six-month period over a 5 year period, at a nominal rate of 9% p.a. The annuity has an annual fee of $300, paid at the beginning of each year. The second annuity pays $1,000 each month, again over 5 years at a nominal rate of 10% p.a.and does not have an annual fee.If each of the annuities cost $50,000, identify which of the...

Your client has identified a viable marketing opportunity and has asked you to identify and document...

Your client has identified a viable marketing opportunity and has asked you to identify and document changes needed to current operations to take advantage of this opportunity. 1. Create a brief that you received from your client, giving background information on their organisation and describing the opportunity (ie create a scenario). (50–100 words) 2. Write your report, showing the criteria you have considered and the changes required in each area. (1 page)

Question