Math/Excel help PLEASE!!!!

I am in the initial stages of starting a Non-profit Charitable trust (with a few other people) that would encourage low income savers to experience market returns that have floored (or guaranteed) returns (as it will be backed by a pool of grantors money that is invested in the market. What I am looking to do is to develop a model that will provide me an idea of what ratio of grantors money to participants (low income savers) is needed to meet the goal of protecting these peoples money. Therefore, I would like to create a model in which I can plug in different ratios of grantor to savers money and different scenarios of market returns needed to guarantee a certain percent of return. So the variables are the following: Grantors money Participants money Market returns Guaranteed rate of return to the low income savers One example would be: Grantors pooled money: 10 Participants money: 1 Guaranteed rate of return for participant: 6%. What is the worst market return that can occur before the Participants money is not fully guaranteed at 6%? My goal would be to be able to tweak any one of these variables on an excel spreadsheet and find what the threshold is to where the savers portion of the money is not safe. Caveat: The savers money will always be liquid in a money market and will not be invested in the stock market. Only the grantors money will be in the market. It would be SO APPRECIATIVE if someone can email and Excel sheet setting up this model to my email or kindly respond here. Thanks so much Gina.

What are you doing exactly? Grantors put their money in the pool and what happens to it? Participants put their money in the pool and what happens to it and what guarantees do you want to give who?

Participants leave their money in a seperate account. I am looking to set up an excel model where I have 4 of these 5 variables, and derive the 5th based on the the forumals inputed Grantors money Participants money Market returns Guaranteed rate of return to the low income savers Grantor/Particpant ratio

Do an example for me. 10 grantors put $1000 in and 1 participant puts $1000 in. The market (stock market, yes?) goes up 20%. Money market interest is 5%. Who gets what?

Hi Joey, Here is an example. Grantors money is 10000 Participants money is X Market return (that the Grantors money was put in) is 5% Guaranteed return for Particpants: 12%. What is the ratio of Grantors to Participants money(or the highest possible amount of Partipant money) that can be put in that will not jeapordize the guarantted 12% return? Thanks for your help. For what its worth, I was given this as the needed formula, but I don’t know how to create an excel model showing such Simple one-period returns: Code: let g = Grantors money let p = Participants money let m = Market returns let r = Guaranteed rate of return to the low income savers let G = g/p = ratio of grantors’ money to participants’ money g * (1+m) ¡Ý p * (1+r) G ¡Ý (1+r)/(1+m) r ¡Ü G*(1+m) - 1 m ¡Ý (1+r) / G - 1

I still don’t get it (which probably means you don’t either). The formula having unprintable characters doesn’t help. If the participant is guaranteed a return, then that guarantee needs to be funded by someone else, presumably the grantors. Why is a grantor doing this and what does he get except the privilege of handing out free ITM puts?

Joey, It is a form of a donation, so the grantors don’t have to receive anything in a form of an investments. I wish I could explain it better, but I don’t know how. Is there a particular facet that I can explain better? thanks.

So a grantor puts in money and all the grantor’s money can go to the return of the participant? In your scenario, > Grantors pooled money: 10 > Participants money: 1 > Guaranteed rate of return for participant: 6%. The particpant makes 0.05 from the money market, so he only needs 0.01 from the grantors. That means that the grantors could get almost completely annihilated losing 99.9% of their money and the participant would get his return. Is that right? As long as we don’t let LEH or BSC or any other bank manage the grantor’s trust, we’re probably okay.

So the foundation that is created gets grants from people who want to donate to promote savings. So any participant who is a low net individual can create a savings account that is left in a money market. There is a pooled account of grantors money. The participant is guaranteed a rate of return, lets say 12%. That 12% is taken out of the grantors pool of money when the participant wants to withdrawal funds. The grantors money is invested in the stock market. I am wondering what ratio of grantors money to particpant money is required to able to take out 12% and remain solvent, if the invested pool of money is returning 2%. Here is another example. The grantors pool of money is 100,000 The Particpants money is 75k. The Grantors return on pooled investment is 4% What rate of return can be paid out to Partipants and still have the grantors capital not depleted? thanks.

Maybe this better explains it So essentially I am trying to create an excel model that has four variables. 1. All grantors money that is pooled into one account 2. All participants money that is held in separate accounts 3. The rate of return that is earned by the grantors pooled account. 4. The rate of return that is promised to the particpants. My goal is to create a model where if three of these variables are given, I can derive the fourth. An example would be: Example 1 There is a combination of 100,000 particpants money. They are promised 10% annual return. There is 75,000 grantors money. Question: What is the minimum rate of return the pooled money can earn in order to not be entirely depleted? Example 2 There is a total of 100,000 partipants money. They are promised 10% annual return. They grantors pooled money earned 2%. Question: What is the amount of grantors money needed to guarantee this rate of return? Example 3: There is 100,000 or grantors money They grantors pooled money earned 4% The Particiapants are guaranteed 12% Question: What is the maximum amount of paripants money we can take on to guarantee the 100k of grantors money is not depleted.?

So if i’m understanding this correctly, the grantors’ money are entirely a donation to the fund, and are essentially a there to provide a cushion / perpetuity? And they receive 0 compensation, correct? My impression is that you are creating a savings program with guaranteed returns. But this would imply continuous growth with compound interest. However this is unsustainable with a simple cushion. Under this assumption, the grantors will either always overfund, or will never have enough funds. Think of it this way… - If the grantors’ pool is larger, but earns less than the required rate, it HAS to be depleted eventually. - If the grantor’s pool earns a higher rate than the required rate, it will eventually grow larger than the necessary amount to cover the required rate. Thus you cannot calculate a minimum size of pool. Your best chance is to limit the amount to a fixed amount of years. Such as a 10 year guarantee. The other way, would be to set it up as simple interest, like bond coupons. This would be set up similarly to scholarship trust funds.

I think whatever purpose you are trying to serve by giving people riskless profits by trying to convince other people to give them money is misguided. The world belongs to the risk takers, not people who hold out their hands and look pathetic.

Yes, it would be treated as perpetuity. Wouldnt the required rate of return have to be larger than the guaranteed rate only if the grantors money relative to the participants money are equal? If there is 1 million dollars of grantors money and 100k of participants money and the partipants were guaranteed 10% return, the grantors money can still earn 2 percent and still have enought to cover the 100k particpants money. How many years would it take for the grantors money to be depleted if the guaranteed rate was that much higher than the required rate? Thanks.

Like i was saying, you need a few more conditions/assumptions in this model. The main one being how long this guarantee lasts. Although on the bigger picture, i’d have to agree with Joey. It would seem unreasonable for people to expect a “gimme” just for savings, and it would certainly be… challenging to find people willing to donate their money when they could be helping out many other causes.