Excel formula for sum of income over multiple years adjusted for inflation
I have the income X, and the number of years in the future Y, and the inflation Z. I can calculate the compound interest simply by CI(Y) = X*(1+Z)^Y for any given year, but how can I construct a formula that adds each years income together for a total sum?
Ex)
X = 1000
Y = 3
Z = 5%
CI(0) = $1000
CI(1) = $1050
CI(2) = $1102,5
CompoundInterestSum(3) = $3152,5
I could do this in code recursively, but I'd like to know the cleaner formula that I can use in Excel. Thanks!
microsoft-excel worksheet-function mathematica
asked Dec 12 at 17:20
jsmars
15528
add a comment |
I have the income X, and the number of years in the future Y, and the inflation Z. I can calculate the compound interest simply by CI(Y) = X*(1+Z)^Y for any given year, but how can I construct a formula that adds each years income together for a total sum?
Ex)
X = 1000
Y = 3
Z = 5%
CI(0) = $1000
CI(1) = $1050
CI(2) = $1102,5
CompoundInterestSum(3) = $3152,5
I could do this in code recursively, but I'd like to know the cleaner formula that I can use in Excel. Thanks!
microsoft-excel worksheet-function mathematica
asked Dec 12 at 17:20
jsmars
15528
There are a lot of formulas -> financial in excel that can accomplish what you desire. However, it looks like CI(1) should be 1050, etc. In otherwords, C(0) should be 1000. Also, your calculation is the principal and interest. therefore your CompoundInterestSum(3) is overstated. If you think not, then I would like to invest in your company...
– gns100
Dec 12 at 17:48
Thanks! You are correct and I've updated to start at a zero-based index. What do you mean by overstated in the example? My actual scenario is calculating total returns on solar power.
– jsmars
Dec 13 at 10:52
add a comment |
I have the income X, and the number of years in the future Y, and the inflation Z. I can calculate the compound interest simply by CI(Y) = X*(1+Z)^Y for any given year, but how can I construct a formula that adds each years income together for a total sum?
Ex)
X = 1000
Y = 3
Z = 5%
CI(0) = $1000
CI(1) = $1050
CI(2) = $1102,5
CompoundInterestSum(3) = $3152,5
I could do this in code recursively, but I'd like to know the cleaner formula that I can use in Excel. Thanks!
microsoft-excel worksheet-function mathematica
asked Dec 12 at 17:20
jsmars
15528
I have the income X, and the number of years in the future Y, and the inflation Z. I can calculate the compound interest simply by CI(Y) = X*(1+Z)^Y for any given year, but how can I construct a formula that adds each years income together for a total sum?
Ex)
X = 1000
Y = 3
Z = 5%
CI(0) = $1000
CI(1) = $1050
CI(2) = $1102,5
CompoundInterestSum(3) = $3152,5
I could do this in code recursively, but I'd like to know the cleaner formula that I can use in Excel. Thanks!
microsoft-excel worksheet-function mathematica
microsoft-excel worksheet-function mathematica
asked Dec 12 at 17:20
jsmars
15528
asked Dec 12 at 17:20
jsmars
15528
edited Dec 13 at 10:50
asked Dec 12 at 17:20
jsmars
15528
asked Dec 12 at 17:20
jsmars
15528
asked Dec 12 at 17:20
jsmars
15528
15528
There are a lot of formulas -> financial in excel that can accomplish what you desire. However, it looks like CI(1) should be 1050, etc. In otherwords, C(0) should be 1000. Also, your calculation is the principal and interest. therefore your CompoundInterestSum(3) is overstated. If you think not, then I would like to invest in your company...
– gns100
Dec 12 at 17:48
Thanks! You are correct and I've updated to start at a zero-based index. What do you mean by overstated in the example? My actual scenario is calculating total returns on solar power.
– jsmars
Dec 13 at 10:52
add a comment |
There are a lot of formulas -> financial in excel that can accomplish what you desire. However, it looks like CI(1) should be 1050, etc. In otherwords, C(0) should be 1000. Also, your calculation is the principal and interest. therefore your CompoundInterestSum(3) is overstated. If you think not, then I would like to invest in your company...
– gns100
Dec 12 at 17:48
Thanks! You are correct and I've updated to start at a zero-based index. What do you mean by overstated in the example? My actual scenario is calculating total returns on solar power.
– jsmars
Dec 13 at 10:52
There are a lot of formulas -> financial in excel that can accomplish what you desire. However, it looks like CI(1) should be 1050, etc. In otherwords, C(0) should be 1000. Also, your calculation is the principal and interest. therefore your CompoundInterestSum(3) is overstated. If you think not, then I would like to invest in your company...
– gns100
Dec 12 at 17:48
There are a lot of formulas -> financial in excel that can accomplish what you desire. However, it looks like CI(1) should be 1050, etc. In otherwords, C(0) should be 1000. Also, your calculation is the principal and interest. therefore your CompoundInterestSum(3) is overstated. If you think not, then I would like to invest in your company...
– gns100
Dec 12 at 17:48
Thanks! You are correct and I've updated to start at a zero-based index. What do you mean by overstated in the example? My actual scenario is calculating total returns on solar power.
– jsmars
Dec 13 at 10:52
Thanks! You are correct and I've updated to start at a zero-based index. What do you mean by overstated in the example? My actual scenario is calculating total returns on solar power.
– jsmars
Dec 13 at 10:52
add a comment |
1 Answer
1
active
oldest
votes
=-FV(z,y,x) or =FV(z,y,-x)
This calculates the Future Value of a stream of amounts based on the interest rate per period, the number of periods, and the amount added each period.
https://support.office.com/en-us/article/FV-function-2EEF9F44-A084-4C61-BDD8-4FE4BB1B71B3
answered Dec 12 at 17:59
BobtheMagicMoose
54439
1
I was looking at this function earlier, but it returns a negative value and it's described as used for loans. Looking into it further it seems the result is the same as long as the value is negated, so if you updated your answer with a - or *-1 for clarity I will mark it as correct. Thanks a lot!
– jsmars
Dec 13 at 10:47
@jsmars, that's strange that it comes out negative. Apparently it has to do with tradition with how finance people think of (+) and (-)... good catch, I'll update
– BobtheMagicMoose
Dec 13 at 19:45
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1383052%2fexcel-formula-for-sum-of-income-over-multiple-years-adjusted-for-inflation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
=-FV(z,y,x) or =FV(z,y,-x)
This calculates the Future Value of a stream of amounts based on the interest rate per period, the number of periods, and the amount added each period.
https://support.office.com/en-us/article/FV-function-2EEF9F44-A084-4C61-BDD8-4FE4BB1B71B3
answered Dec 12 at 17:59
BobtheMagicMoose
54439
1
I was looking at this function earlier, but it returns a negative value and it's described as used for loans. Looking into it further it seems the result is the same as long as the value is negated, so if you updated your answer with a - or *-1 for clarity I will mark it as correct. Thanks a lot!
– jsmars
Dec 13 at 10:47
@jsmars, that's strange that it comes out negative. Apparently it has to do with tradition with how finance people think of (+) and (-)... good catch, I'll update
– BobtheMagicMoose
Dec 13 at 19:45
add a comment |
=-FV(z,y,x) or =FV(z,y,-x)
This calculates the Future Value of a stream of amounts based on the interest rate per period, the number of periods, and the amount added each period.
https://support.office.com/en-us/article/FV-function-2EEF9F44-A084-4C61-BDD8-4FE4BB1B71B3
answered Dec 12 at 17:59
BobtheMagicMoose
54439
1
I was looking at this function earlier, but it returns a negative value and it's described as used for loans. Looking into it further it seems the result is the same as long as the value is negated, so if you updated your answer with a - or *-1 for clarity I will mark it as correct. Thanks a lot!
– jsmars
Dec 13 at 10:47
@jsmars, that's strange that it comes out negative. Apparently it has to do with tradition with how finance people think of (+) and (-)... good catch, I'll update
– BobtheMagicMoose
Dec 13 at 19:45
add a comment |
=-FV(z,y,x) or =FV(z,y,-x)
This calculates the Future Value of a stream of amounts based on the interest rate per period, the number of periods, and the amount added each period.
https://support.office.com/en-us/article/FV-function-2EEF9F44-A084-4C61-BDD8-4FE4BB1B71B3
answered Dec 12 at 17:59
BobtheMagicMoose
54439
=-FV(z,y,x) or =FV(z,y,-x)
This calculates the Future Value of a stream of amounts based on the interest rate per period, the number of periods, and the amount added each period.
https://support.office.com/en-us/article/FV-function-2EEF9F44-A084-4C61-BDD8-4FE4BB1B71B3
answered Dec 12 at 17:59
BobtheMagicMoose
54439
edited Dec 13 at 19:46
answered Dec 12 at 17:59
BobtheMagicMoose
54439
answered Dec 12 at 17:59
BobtheMagicMoose
54439
answered Dec 12 at 17:59
BobtheMagicMoose
54439
54439
1
I was looking at this function earlier, but it returns a negative value and it's described as used for loans. Looking into it further it seems the result is the same as long as the value is negated, so if you updated your answer with a - or *-1 for clarity I will mark it as correct. Thanks a lot!
– jsmars
Dec 13 at 10:47
@jsmars, that's strange that it comes out negative. Apparently it has to do with tradition with how finance people think of (+) and (-)... good catch, I'll update
– BobtheMagicMoose
Dec 13 at 19:45
add a comment |
1
I was looking at this function earlier, but it returns a negative value and it's described as used for loans. Looking into it further it seems the result is the same as long as the value is negated, so if you updated your answer with a - or *-1 for clarity I will mark it as correct. Thanks a lot!
– jsmars
Dec 13 at 10:47
@jsmars, that's strange that it comes out negative. Apparently it has to do with tradition with how finance people think of (+) and (-)... good catch, I'll update
– BobtheMagicMoose
Dec 13 at 19:45
1
1
I was looking at this function earlier, but it returns a negative value and it's described as used for loans. Looking into it further it seems the result is the same as long as the value is negated, so if you updated your answer with a - or *-1 for clarity I will mark it as correct. Thanks a lot!
– jsmars
Dec 13 at 10:47
I was looking at this function earlier, but it returns a negative value and it's described as used for loans. Looking into it further it seems the result is the same as long as the value is negated, so if you updated your answer with a - or *-1 for clarity I will mark it as correct. Thanks a lot!
– jsmars
Dec 13 at 10:47
@jsmars, that's strange that it comes out negative. Apparently it has to do with tradition with how finance people think of (+) and (-)... good catch, I'll update
– BobtheMagicMoose
Dec 13 at 19:45
@jsmars, that's strange that it comes out negative. Apparently it has to do with tradition with how finance people think of (+) and (-)... good catch, I'll update
– BobtheMagicMoose
Dec 13 at 19:45
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1383052%2fexcel-formula-for-sum-of-income-over-multiple-years-adjusted-for-inflation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
There are a lot of formulas -> financial in excel that can accomplish what you desire. However, it looks like CI(1) should be 1050, etc. In otherwords, C(0) should be 1000. Also, your calculation is the principal and interest. therefore your CompoundInterestSum(3) is overstated. If you think not, then I would like to invest in your company...
– gns100
Dec 12 at 17:48
Thanks! You are correct and I've updated to start at a zero-based index. What do you mean by overstated in the example? My actual scenario is calculating total returns on solar power.
– jsmars
Dec 13 at 10:52