+ Reply to Thread
Results 1 to 7 of 7

Loan schedule when I know the monthly payment amount?

  1. #1
    Registered User
    Join Date
    02-22-2010
    Location
    Red Deer, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    5

    Loan schedule when I know the monthly payment amount?

    I have a large amount owed to me and the debtor has suggested a payment plan where he pays a monthly amount for a period of time and then the lump sum which is continuing to accrue interest during the repayment period. I want to be able to use a template if possible to input this, but all the ones that I see generate the loan payment amount and I cannot put this in. Can I not use the templates and build my own spreadsheet?

    Any ideas?

    For example, my loan amount is $50,000, the monthly payment is $500 for a year and the interest is 18%. How do I determine what the lump sum payment is at the end of the 12 months including interest using these templates?

    thanks
    Jo-Anne

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Loan schedule when I know the monthly payment amount?

    Jo-Anne
    Look at the attached and see if that works for you.

    Alan
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Loan schedule when I know the monthly payment amount?

    Make an amortization table:

    Please Login or Register  to view this content.
    Last edited by shg; 02-01-2011 at 08:04 PM.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Loan schedule when I know the monthly payment amount?

    Hi Jo-Anne and welcome to the forum.

    I know there are lots of Payment and Loan formulas in Excel, but you can do this one by hand.

    See the attached.

    With your numbers they will owe you 53,190.89 after 12 months.

    Don't believe the formulas... Figure out the first month's interest and payment and how much they owe you on month 2. Do a few formulas on the top and pull them down for 12 months.

    I love it when we all get different answers - looking at the above. Maybe this is we all work for free.
    Attached Files Attached Files
    Last edited by MarvinP; 02-01-2011 at 08:09 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Loan schedule when I know the monthly payment amount?

    Hi,

    I used the Excel Mortgage Amortization template as the base for this.

    Regards
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Loan schedule when I know the monthly payment amount?

    My answer is wrong!!!
    When I pulled down the formula I Paid 500 the first month and 501 the second and 502 the third month.

    I'm wrong first!!!! Maybe this is why I don't have a job!!
    Last edited by MarvinP; 02-02-2011 at 12:46 PM.

  7. #7
    Registered User
    Join Date
    02-22-2010
    Location
    Red Deer, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Loan schedule when I know the monthly payment amount?

    Thanks everyone! This does work like a charm - now just to get the customer paying us back!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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