+ Reply to Thread
Results 1 to 4 of 4

Payment Schedule Formulas Excel

  1. 09-18-2019, 09:42 AM #1
    lanespoli
    lanespoli is offline
    Registered User
    Join Date
    11-27-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2010
    Posts
    32

    Payment Schedule Formulas Excel

    I have to put together a "schedule" that reconciles to my balance sheet each month showing the beginning balance of each loan, the total monthly payments and the ending loan balance both long term and short term for whichever month we are reconciling. I have a 10 year loan for 1,940,000 that started on November 7, 2016 and ends on October 30, 2026 with an interest rate of 4.04%. My loan payments every month including interest are 11,856.10. Does anyone know the formulas I would plug in to generate this "schedule"?

    I tried an ammortization one online but that isn't calculating the payments correctly for some reason so I am trying to generate my own in excel. Thank you.
    Reply With QuoteRegister To Reply
  2. 09-18-2019, 11:50 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Payment Schedule Formulas Excel

    From your title and description, this is an Excel question, but you posted it in the Access forum.

    I will move it for you, but please, PM me if you really did want it in the Access forum?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 09-19-2019, 12:08 AM #3
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Payment Schedule Formulas Excel

    Something doesn't add up

    Repayments of 11,856.10 would be too little to pay off 1,940,000 loan @ IR 4.04%

    in fact
    10 year loan paid off monthly = 120 repayments
    11,856.10 x 120 = 1,422,732...ie your repayments wont even reach the principal amount if it was 0% IR
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  4. 09-19-2019, 11:03 PM #4
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,895

    Re: Payment Schedule Formulas Excel

    Based upon Humdingaling's analysis, it seems you may have a balloon payment at the end. Is this the case?
    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
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Fill down principal payment & interest payment formulas in Excel
    By Silverthunder in forum Excel General
    Replies: 1
    Last Post: 02-04-2016, 01:03 PM
  2. [SOLVED] Last date of a payment schedule
    By trosasco in forum Excel General
    Replies: 6
    Last Post: 02-05-2014, 09:37 AM
  3. Payment Schedule
    By NYKnicks in forum Excel General
    Replies: 2
    Last Post: 08-20-2013, 04:49 PM
  4. Monthly Payment Schedule
    By robstark in forum Excel General
    Replies: 4
    Last Post: 02-18-2013, 05:11 PM
  5. [SOLVED] Complex Payment schedule
    By Christopherdj in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-17-2012, 11:51 PM
  6. Excel Loan Payment Amortization Schedule
    By gacollege in forum Excel General
    Replies: 6
    Last Post: 11-17-2010, 12:18 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1