ROUND function

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Hello forum friends, the formula below returns the result I need but, it isn't rounded to two decimal places. Instead, it gives me 1342.751667. I have been beating myself up over this trying to figure out what I am doing wrong. Any guidance is appreciated.

=ROUND(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),2)+(ROUND((INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084))+INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084+INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),2)+ROUND(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),2)+ROUND(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),2))/12
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,979
Office Version
  1. 365
Platform
  1. Windows
@Joe4 thanks, I will try that.
You are welcome.

I find that if you have something big and complex that doesn't work, one good way to fix it is to start over from scratch, and rewrite it starting just with the first part. When you get that working, add the second, and if that works, add the third, etc.
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@Joe4 @Misca @AhoyNC Joe, I did as you suggested and re-wrote the formula from scratch, making sure each section worked as I went along. I also got rid of ALL of the ROUND(s) in my original formula. Now everything is working as anticipated. Of course, I'll never really know exactly what was causing the problems but as you said, most likely a misplaced parenthesis or something along those lines. Thanks to all of you that stepped in to try and help me, I am super relieved that this one is now working! Cheers!

Posting the formula below since it's working now (I had to split the formula into two because of the 8192 character limit!!! I know, it's likely some folks on here will laugh and I'm sure there's probably a simpler way to do it but my Excel skills are very limited. :p

PART 1
=CONCATENATE("Let's assume "&IF(personal_info!C9=0,"Name",personal_info!C9)&" starts "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" CPP at age 65 but continues to work and make CPP contributions. Using the "&YEAR(TODAY())&" maximum retirement benefit of "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")&" per month and"," YMPE of "&TEXT(INDEX(CPP_OAS[Thresholds],MATCH("YMPE",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")&", we can calculate "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" annual PRB at age 66 at "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")&"."," If "&IF(personal_info!C9=0,"Name",personal_info!C9)&" continues to work past age 66, "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" PRB from the previous year is added to the current year's PRB for "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" lifetime and like the CPP, the PRB is indexed to inflation! However, the good news doesn't end there. From age 65 to 70, an additional 8.4% per year (.7% per month) is added to your PRB! So, assuming "&IF(personal_info!C9=0,"Name",personal_info!C9)&" keeps working until "&IF(personal_info!F9="M","he's",IF(personal_info!F9="F","she's","they're"))&" 70...

", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 67 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")&" for a total of $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")&".

", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 68 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*0.025*12*1.084,"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")&" for a total of $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")&".

", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 69 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")&" for a total of $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")++TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")&".

", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 70 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")&" for a total of $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),"$#,##0.00")&".")

PART 2
=CONCATENATE("If "&IF(personal_info!C9=0,"Name",personal_info!C9)&" continues to work after reaching age 70, "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" will no longer contribute to CPP but now "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" will receive an additional $"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),"$#,##0.00")&" (indexed to inflation) every year for the rest of "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" life! "&IF(personal_info!C9=0,"Name",personal_info!C9)&"'s monthly CPP retirement benefit is now at $"&ROUND(TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")+(TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*0.025*12*1.084,"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0715*(0.025*12*1.084),"$#,##0.00")+TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0965*(0.025*12*1.084),"$#,##0.00"))/12,2)&".", " If "&IF(personal_info!C9=0,"Name",personal_info!C9)&" lives to 87 years of age, "&IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))&" total accumulated PRB (taking inflation into account) would be "&TEXT(INDEX(Tax_Credits[Federal],MATCH("CPP Maximum Contribution",Tax_Credits[Tax Credits (dollars)],0))*5*3+262.23,"$#,##0.00")&". This is certainly much more than the "&TEXT(INDEX(Tax_Credits[Federal],MATCH("CPP Maximum Contribution",Tax_Credits[Tax Credits (dollars)],0))*5,"$#,##0.00")&" "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" would have received by not contributing.", " Even if "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" invested the "&TEXT(INDEX(Tax_Credits[Federal],MATCH("CPP Maximum Contribution",Tax_Credits[Tax Credits (dollars)],0))*5,"$#,##0.00")&" "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" (which we all know most people won't do), "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" would need an unlikely 6% return (compounded annually) to grow it to "&TEXT(INDEX(Tax_Credits[Federal],MATCH("CPP Maximum Contribution",Tax_Credits[Tax Credits (dollars)],0))*5*3+262.23,"$#,##0.00")&" in the same amount of time."," Careful consideration should be given to how much you would gain by continuing to contribute. And remember, your employer is also matching your contributions on your behalf.")


ReplyForward
 

Watch MrExcel Video

Forum statistics

Threads
1,129,867
Messages
5,638,757
Members
417,051
Latest member
arthur_w

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top