+ Reply to Thread
Results 1 to 7 of 7

Loan Functions

  1. #1
    Registered User
    Join Date
    11-09-2017
    Location
    Nairobi
    MS-Off Ver
    2016
    Posts
    3

    Post Loan Functions

    Files Needed: Tutorial 08-Coach Cafe Business Plan.xlsx

    The Coach Café is a small neighborhood café that serves vegetarian breakfasts and lunches. The owner of the café, Keith Watson, started the café with $50,000 of his own money. Now that he’s up and running, he realizes that he needs additional financing. His parents have offered to loan him an additional $50,000 to be repaid within five years. Keith needs to analyze the loan payments and terms to ensure he can make the payments required. He has started a workbook and asks you to complete a Loan Analysis and Amortization Schedule with data related to the loan.

    1. Start Excel and open Tutorial 9_Coach CafeLoan Analysis.xlsx.
    2. Save the spreadsheet as Tutorial 9_Last Name_Coach CafeLoan Analysis.xlsx. (Replace “Last Name” with your last name.)
    3. In the Loan Analysis worksheet, in the range C7:J10, enter or calculate the loan analysis data you will use as the basis for additional calculations. Kevin wants to borrow $50,000 over a period of 5 years and make 12 payments each year. His parents have asked for an annual interest rate of 5.35%. Enter this value in cell C4.
    4. Based on the data you entered in step 3, make the following calculations:
    a. In cell J7, use the PMT function to calculate Kevin’s monthly payments on the $50,000 loan.
    b. In cell E8, delete the current value, then use the NPERfunction to calculate the number of monthly payment periods required to pay off the loan if the monthly payment entered in cell J8 is$1,200. Remember to enter $1,200 as a negative number in cell J8.
    c. In cell D8, calculate the total number of years required to pay off the loan.
    d. In cell H9, use the PV function to calculate the largest loan the café could repay in 5 years if the monthly payments were $1,500.
    e. In cell I10,use the FVfunction to calculate the principal at the end of 5 years with monthly payments of $1,500.
    5. Shade cells J7, E8, D8, H9, and I10 with the fill color of your choice.
    6. In the Amortization worksheet, reference the data from the appropriate cells in row 7 of the Loan Analysis worksheet to enter the data required for cells B8 to G8. In cell H8, use the PMT function to calculate the monthly payment, then change the loan (PV) to $60,000.
    7. Complete the Amortization schedule using the cell addresses from row 8. Use absolute references where needed. In cell D12, enter the loan amount as the Remaining Principal, then complete the schedule as follows:
    a. Use the PPMT function to calculate the Principal payment for each month
    b. Use the IPMT function to calculate the Interest payment for each month.
    c. Calculate the total payment for each month.
    d. Reduce the principal owed for each month by the amount paid in the previous month.
    e. Copy the formulas for all five years of the loan period.
    f. Apply shading of a different shade to the row containing the value of the last payment (the last value showing as a positive value in column D).
    8. Below the Amortization schedule, calculate the cumulative interest and principal payments in the appropriate cells as follows:
    a. Use the CUMPRINC function to calculate the cumulative principal payments in each of the five years of the loan. Include absolute references to loan conditions as needed.
    b. Use the CUMIPMT function to calculate the cumulative interest payments in each of the five years of the loan.
    c. Calculate the remaining principal at the end of each of the five years (row 80).
    d. At the top of the worksheet, in cells C5 and D5, calculate the total principal payments and interest payments. Show the results as positive values.
    9. Go to the Loan Analysis worksheet, change the Annual Interest Rate in cell C4 to 6.25%, then note the total interest paid in D5 of the Amortization worksheet.
    10. Save the file and submit it to your instructor.

  2. #2
    Registered User
    Join Date
    11-09-2017
    Location
    Nairobi
    MS-Off Ver
    2016
    Posts
    3

    Re: Loan Functions

    Some one please assist on those functions.

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Loan Functions

    Is that not your instructor's job?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  4. #4
    Registered User
    Join Date
    11-09-2017
    Location
    Nairobi
    MS-Off Ver
    2016
    Posts
    3

    Re: Loan Functions

    Yes it is. I was asking for help in the homework to prepare for an exam

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,632

    Re: Loan Functions

    You need to ask for what you are stuck on. It is not reasonable for someone to do all your homework, and you don't learn. If there is a specific bit you are struggling on, give us your attempt and someone will help you solve it. I am sure for example you can save the spreadsheet with your name in the title!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,306

    Re: Loan Functions

    Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

    We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

    If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,306

    Re: Loan Functions

    Thanks for the rep and for telling me you've worked it out.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. loan sheet with past due functions
    By mshchanin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-15-2017, 03:00 PM
  2. NPV for Loan
    By TiffanyWong in forum Excel General
    Replies: 3
    Last Post: 09-05-2016, 06:08 PM
  3. Replies: 3
    Last Post: 07-24-2016, 07:33 PM
  4. [SOLVED] Secondary Loan Pricing Calculation (price of loan being bought/sold)
    By Romsky in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2013, 07:02 PM
  5. OutstandingBalance of a loan
    By Marvwade in forum Excel General
    Replies: 1
    Last Post: 01-14-2012, 11:44 PM
  6. [SOLVED] loan amortization template with loan start date AND first payment
    By Lisa W in forum Excel General
    Replies: 0
    Last Post: 01-30-2006, 06:30 PM
  7. Replies: 0
    Last Post: 02-03-2005, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1