trying to make excel do my math... percentage payouts per year change based on length of..

[eshirk33]

So I’m admittedly a spreadsheet novice, so I’m not necessarily sure how to even ask this question - but I’ve learned a lot over the past few weeks on this project I’m working on, and this is the first thing I haven’t been able to figure out on my own.
What I’m trying to create now is a formula that will show me the yearly cost of a contract based on a few variables.
The three figures I’m looking to enter into the sheet are Total Amount, Bonus and Contract length.
The annual cost of the contract is always an equal portion of the bonus, and then the base on a sliding scale.
For instance, if there’s a contract that is total of 5 million over 5 years, and the bonus is 1 million, the 1 million would be paid evenly over the 5 years (200k per year), but the remaining 4 million is paid out on a set schedule.
Here’s where it gets tricky for me – the schedule changes depending on the length of the contract, so I’d like the formula to know which to use based on my ‘contract length’ entry.
Here’s the schedule –
4yr 16% 21% 27.25% 35.75%
5yr 14.28% 14.46% 18.29% 23.21% 29.76%
6yr 7.83% 10.19% 13.24% 17.21% 22.38% 29.05%
7yr 5.54% 7.3% 9.46% 12.3% 16.08% 20.95% 27.7%


Anyway, I know how to do this manually, but I’m not sure how to make excel do it.
The annual payment is bonus/contract length PLUS total amount MINUS bonus x the appropriate percentage.
Can anyone help with this?

[Glenn Kennedy]

Without seeing a sample sheet, we could waste a lot of time trying to guess the layout that you have right in front of you.

Will you please attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Alf]

Interesting, I guess I would do something like this:

table.jpg

where "table" is defined as the range B3:I6 and the number in the formula i.e. 2 is the second column in "table" range and has to be changed to 3, 4, 5, 6, and 7 in VLOOKUP formula.

Alf

[Phuocam]

Try:

=VLOOKUP($B$10;data;COLUMNS($B$2:C2);0)*$C$10

[eshirk33]

Glenn - attaching a sample here - If you need something else, or this isn't what you want, let me know.
Thanks!

[JeteMc]

Welcome to Excel Forum.
For the set up provided in the file attached to post #5 the following formula may be pasted into cell B6, entered, and copied down and across:

Please Login or Register  to view this content.

Note that there is a discrepancy between the expected value of the 6 year sample payout and the actual values returned by the formula, the cause of which is that the percentages for the 6 year pay out do not sum to 100%.
Let us know if you have any questions.

[eshirk33]

Thank you everyone - JeteMc's response was especially helpful - the formula works. When i go to paste into my uploaded doc, however, it doesn't work - but it works in the version I downloaded from the post.

Point is, I don't need to waste anyone's time on this, I will try to figure out why it won't work on my original sheet. In the meantime, I can just use the working sheet and copy/paste the info. Very grateful for the help.

Thanks again

[JeteMc]

When i go to paste into my uploaded doc, however, it doesn't work - but it works in the version I downloaded from the post.

I should have noted that I made some changes in the table in A14:H18 such as just using only numerical values in A15:A18 and dividing the values in B15:H18 by 100 then formatting as percentages. I apologize for omitting that information in my previous post.
Let us know if you have any questions/problems, be glad to help where we can.