Amortization Schedule for Business Purchase and Monthly Savings Calculation

Part A Instructions

You’ve been interested in entrepreneurship as long as you can remember. As a kid you started micro-businesses all the time. After your first “Lemonade Stand”, you started mowing neighbourhood lawns in the summer and shovelling driveways and walks in the winter. Once you were old enough to drive, you started shuttling seniors to doctor’s appointments for tips and tutored accounting students after school. From your 12th birthday on, you’ve averaged a net profit after expenses of $500 per month. At the end of each month, you’ve deposited that money into a self-managed brokerage account, which returns 10% annually. You’ve continued running your businesses and depositing your monthly profits throughout your time here at ACC.

When you graduate from ACC’s School of Business on May 31st 2023, you’re going to purchase your own business using your investment savings as a down payment.

1. State your birthdate and then state the birthdate when you reach twelve years of age. Using the month of your twelfth birthday and the graduation date from above, calculate the number of months you have been saving and the amount of down payment you’ll have available to put toward your new business. Use the nearest whole month when rounding.

 

2. Search the internet for a business for sale in Canada worth at least $500,000.

 

3. Find the Bank of Canada’s website and locate the Prime Rate for the week/day you are working on your project. Due to the high-risk involved, commercial financing quotes are prepared on a case-by-case basis. For the purposes of this project, you will assume that your quoted lending rate is Prime +6% percent annually, and your bank will finance the purchase using your down payment (from your $500 per month savings). Be sure to clearly identify the rate used in your calculations.

 

4. Use your prime +6% rate, compounded monthly, and a 25-year term to calculate a monthly payment, and then construct an amortization schedule.

 

5. If you wanted to pay off the entire loan after 10 years, what would you owe?

 

6. Using the same rate but compounded quarterly, change the term to 20 years, recalculate the monthly payment and create a second amortization chart.

 

7. If you wanted to pay off the entire loan after 10 years, what would you owe?

 

8. Identify and explain one positive benefit and one potential negative consequence of choosing to pay off the business in 20 years, instead of 25 years.

Part B Instructions

 

9. If you are taking Software Apps, you will have or will be constructing an amortization schedule on a business purchase. You may use the same Excel spreadsheet template for this project. If not, you should structure your amortization schedule in the same manner as what is described in class and the course pack.

 

10. Organize your work neatly within the same spreadsheet. Summaries of key findings should be on one worksheet, while amortization schedules on completely different worksheets.

 

11. Use Excel’s FV function to calculate the amount of your down payment upon graduation and the PMT function to calculate the mortgage payment.

 

12. How you submit your work is up to you. If you use Word, please show details of your calculations. This can be done with an Excel spreadsheet or a table within Word. However, just showing tables is not enough. Details of calculations, where the numbers came from, must also be provided. Make sure your information is neat, organized, and final answers are clearly identified and easy to find. If you’re using the BAII plus calculator, you must indicate the financial buttons used and in what order, otherwise you must show the formulas you’ve chosen along with calculation details.

 

13. Make sure to clearly identify your birth month, day and year, along with the year of your 12th birthday to coincide with the graduation date (also state), so your days between dates calculation can be substantiated.

Instead of buying your own business, you decide to use the down payment savings to chase your other passion, surfing. You’re going to move to the surfing capital of Canada, Tofino B.C., and surf every day that you can. In order to do this, you must live within a tight budget. You’ve figured out, through scrimping and saving, you can live on $4,000 Canadian per month.

1. Assume you deposit your down payment into a savings account that earns 1.5% compounded semi-annually.

 

2. Now that your money has been deposited into the savings account, how many months will your money last if you withdraw $4,000 each month? Assume you have no other additional income deposited into the same account. Why would you have other income, you’re surfing every day.

 

3. You may submit your work in another clearly marked tab of your original Excel spreadsheet from Part A, but all information within the spreadsheet must be clearly identified and organized.

 

4. You may also submit your work in a Word document, but all information and work must be clearly identified. If you use the BAII plus calculator, you must show your work. This includes the calculator buttons used. Otherwise, formulas and calculation details are required.

1. It’s a best practice to create a checklist for all the things you need to ensure you aren’t missing any important information.

2. How you present your work is critical. It needs to be organized, be free of all grammatical and spelling errors, and it needs to be attractive. All key findings and answers to questions need to be emphasized and standout so that it’s easily found and identifiable.

3. If any information requested is not presented clearly, can’t be found, or calculations can’t be seen/followed, no part marks will be awarded.

4. Although parts of this assignment are being used for two different courses, the learning outcomes are different. It is your responsibility to distinguish the differences and ensure that each submission meets the requirement for each applicable course.

5. You must upload your document to the Moodle drop box titled “Financial Math Project”, by Sunday, December 12 th at 11:59 pm. Use your full name when naming files that are to be  uploaded to Moodle.