+ Reply to Thread
Results 1 to 1 of 1

How can I capture different instalments correctly for revenue/cash flow?

  1. #1
    Registered User
    Join Date
    Amsterdam, holland
    MS-Off Ver
    Excel 2003

    Question How can I capture different instalments correctly for revenue/cash flow?

    I have a sheet for students who study and pay for different programmes at different times in the year. They have a perfectly free choice of instalment payment plan but the more instalments they choose the more they pay.
    In my sheet I am attempting to see what cash flow would be like for students who join and to correctly capture the right payments at the right time throughout the five years years, of old students and new students.
    For example in 2010 october, D18-D22 takes care of the spread of instalments. Columns B-G and rows 3-10 are to calculate what the actual instalment price will be and the main factors are J2-J4.

    Clearly as a beginner i should never have started this - but not only does the PMT give a negative amount (?) but I cannot seem to have the correct payments flow forward through the months so that i can capture the still outstanding payments due for older students and capture the new activity for the new students. The date they start triggers their payment schedule. so when 10 students join in october
    a percentage will pay one instalment only so 10% of 10 students is 1 student
    35% will pay 2 instalments (over a two year period that means 2 payments over the two years. So october 2010 and october 2011) should have a payment due
    25% will pay 4 instalments (so over two years that means 2 payments in the first year and two in the second), 25% in 8 instalments that means 4 payments in the first year and then four in the second and 12 instalments, 6 in the first year 6 in the second and their start date triggers when their payments start. So an October start will only have a second payment occuring that year in the 12 instalment scenario.

    The result does not need to be beautiful but i would like it to be accurate. Can anyone help, i have emptied my sheet more or less but just still show the structure?


    (i had posted this earlier but could not see it any longer and had no message, so i guess i put it in the wrong place)

    Attached Files Attached Files
    Last edited by juliad; 11-18-2010 at 04:44 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread


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