• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum of cashflows according to sales curve and other assumptions

D

dgoytr

New Member
Hello guys,

Imagine I have multiple types of homes (home 1, 2, 3, 4 etc), with different down payments and remaining values to be payed in "n" years. And these disbursements are controlled by a Sales Curve.

I need to find a formula to sum these different cashflows all in one go.

Please find attached an example sheet.

Any doubts, please let me know.
 

Attachments

Hey vletm!

Solved it manually.

Please find attached another example showing the calculations manually.

The sales curve is the trigger to initializing the payments anually.
- (a) if year 1 the sales curve is 100%, then I will recieve 100% of downpayment in that year.
- (b) if year 1 is 20% and year 2 is 80%, then for homes {1,2}, I will receive 20% of downpayment in year 1 and 80% of downpayment in year 2.

The tricky part is regarding remaining values, because the value can be divided and payed in "n" years.
- (c) if the sales curve in year 2 is 100% and the remaining value is payed 2 installments, then I will receive the first installment in year 2 and the second installment in year 3.
- (d) if the sales curve in year 2 is 20% year 3 is 80%, and the remaining value is payed 2 installments, then in year 2 will receive 20% of the first installment and in year 3: 20% of the second installment, added to 80% of the other first installment, and finally in year 4 will receive 80% of the other second installment.
 

Attachments

Hey vletm! Thanks for replying and for taking the time.

The tricky part in your spreadsheet is the "remaining value" starting in D10.

You need to consider the "Remaining value installments" in B6:C6.

Because when I change the installment for any Home 2 from 2 to 3 installments, my number of years receiving this cashflow should increase by one year.
 
D10 gotta be empty!
Where were connection between Your solution and those B6:C6?
Your sample solution should give a clear image ... what?
 
vletm said:
D10 gotta be empty!
Where were connection between Your solution and those B6:C6?
Your sample solution should give a clear image ... what?
Click to expand...
please take a look at cells g22:g25 and h22:h25, these cashflows are dependent on the number of installments in B6 and C6
 
Hey vletm, I saw your spreadsheet :D if you change cell C6 to value=3, the total value in D16 should be the same, but is different.
 
I see ...
that You've skipped basic Forum Rules:
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
from
K

Thread 'Site Rules - New Users - Please Read'

Hi all,

Welcome to the Chandoo.org Forums.

Posting Rules & Etiquette

The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge.

The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in.

Please take a few minutes to read this list of Rules, Procedures and Etiquette's for posting here at the Chandoo.org Forums.

How to get the Best Results at Chandoo.org
  • Use Relevant words in the Title and in the tag Box, This will aid future searches...
  • Like
Take care
 
You must log in or register to reply here.
Back
Top