+ Reply to Thread
Results 1 to 2 of 2

matching an amortization schedule

  1. #1
    Registered User
    Join Date
    02-16-2010
    Location
    Hawai'i, United States
    MS-Off Ver
    Excel 2007
    Posts
    1

    matching an amortization schedule

    im trying to create a somewhat active balance book. colum A=date(monthly); column B=income; column C=expense; column D=balance. on another sheet i have an amotization schedule(taken from the default template). i want to have my balance book reference the amortization schedule when it starts and finishes. so if my amortization schedule starts in April 2010 then on my balance sheet i want the first payment to appear in column C in the corresponding row where the date in column A matches the date of teh first payment of the amortization schedule. also with the idea that the balance book may start at different dates. A1 might equal Feb 2010 or maybe August 2011. if teh date in column A = the date of a payment in the amortization schedule, i want it to report the payment in column C of the same row.
    Attached Images Attached Images

  2. #2
    Forum Contributor
    Join Date
    08-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    149

    Re: matching an amortization schedule

    It'd be nice if your amort sched had three more fields in the header... last payment date, payment amount and last payment amount. You could do this by using an OFFSET from your first row of data to go down the proper number of rows for years*pmts per year to return the correct last date and payment.

    Then your balance sheet could have a formula like:
    if(and(A3>=first pmt date,A3<last pmt date),payment,0)+if(A3=last pmt date,last payment,0)

    Another option would be a vlookup. But making it return zeros outside the payment months makes for a more complicated formula.

+ 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