+ Reply to Thread
Results 1 to 8 of 8

Mortgage Formula

  1. #1
    lsmft
    Guest

    Mortgage Formula

    Trying to get a formula to automatically show a balance on a R/E Loan.
    $140,000.00 Original Note @ 7.5% interest.
    Paid 10% up front. ($14,000.00)
    Starting 1/1/05, monthly pymts of $800.00.
    How can I set up a formula to automatically reduce the balance of the loan after each monthly payment is made?
    Thank you.

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Take a look at this link it may help?

    http://www.vertex42.com/ExcelTemplat...readsheet.html
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    lsmft
    Guest
    Thank you Old Chippy, but that is not what I was leaning toward.
    I have a worksheet set up:
    Column I-5 thru I-157 is the months numbered. (1 thru 157)
    Column J-5 thru J-157 shows the months. (Jan. 2005 thru Sept. 2017)
    Column K-5 thru K-157 shows $800.00 in each cell.
    Column L-5 thru L-157 shows a running balance of monies paid in.
    I was thinking of a "Lookup" formula that would take the original note of 140,000.00, less the 14,000.00 down, and have a cell to reflect the remaining balance of the note after each month's 800.00 pymt. I'm having a problem getting a "Lookup" formula to work here and I'm not sure why.

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Can you provide a zipped sample of your data. This will be easier for all of us to understand what you are trying to do, then hopefully a solution can be found.

    To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this to your post.

  5. #5
    lsmft
    Guest
    This is a simplified version of what I'm looking for.
    Cell "B-5" will be where the balance owed on the loan will be shown. It should be automatically updated on the 1st of each month.
    Thank you.
    Attached Files Attached Files

  6. #6
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Try this formula in B5

    =B3-VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY()),1),E6:G155,3,FALSE)

  7. #7
    lsmft
    Guest
    Thank you Oldchippy,
    That will work fine.

  8. #8
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Glad it worked for you - thanks for the feedback

+ 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