Calling all NPV formula gurus! I have an attorney who has a spreadsheet that is using the NPV formula. He is not getting the expected results. His main clue that he is not getting the correct result is that cell D45, he says, should be the same as B45. AFter reading the NPV formula information, I am not even sure that he is using this correctly. Can someone take a look at the attached spreadsheet and let me know what you think?
Thank you!!!
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
Net Present Value Formula (XP)
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Net Present Value Formula (XP)
- This topic has 9 replies, 3 voices, and was last updated 21 years, 7 months ago.
AuthorTopicWSmnehring
AskWoody LoungerSeptember 24, 2003 at 2:40 pm #394075Viewing 0 reply threadsAuthorReplies-
WSsdckapr
AskWoody LoungerSeptember 24, 2003 at 3:36 pm #719389I don’t understand why the values in E7 and E8 do not use the NPV calc. If you continue the calc upward, you get different numbers than in these cells.
The values in B45 and D45 should NOT be the same. If I am going to get $195,3000 a month from now and the interest rate is 8% annual, then the present value of my (future earnings) are 195300/(1+.08/12) = 194,006.62 which is what excel calcs using the NPV number. Essentially it says that the $195k you PLAN to get at March 31 (end of period) is NOW (on March 1) worth $194k.
If he wants them to be the same he needs a different calc! (if you put =D45 in B45 they will be equal!
)
NPV uses value at end of period, so maybe his rows need to be offset.What is he trying to calculate?
Steve
-
WSchipshot
AskWoody LoungerSeptember 24, 2003 at 4:48 pm #719429It looks like he’s trying to calc the present value of remaining payments, but his use of the NPV function is careless. Like Steve said, NPV assumes the payment array starts one period after the calculation date. For example, D19 has 4,811,138 and he has it labeled 11/1/04. That $4.8 million is actually the 10/1/04 value of all of the 195,300 payments. So the 194,006 is the 2/1/07 value of the 3/1/07 payment. To move the calc date ahead one month, he can multiply his NPV function by (1+.08/12). So the 11/1/04 value of all of the 195,300 payments is $4.8 million * (1+.08/12) and the 3/1/07 value of the 3/1/07 payment is 194,006*(1+.08/12) = 195,300.
I also agree that It is very fishy that cells D7 and D8 have hard coded numbers and not the formula. The formula gives considerably larger numbers than what’s typed in there. Is the lawyer trying to understate the value of the payments?
BTW, I hate merged cells.
-
WSchipshot
AskWoody LoungerSeptember 24, 2003 at 4:48 pm #719430It looks like he’s trying to calc the present value of remaining payments, but his use of the NPV function is careless. Like Steve said, NPV assumes the payment array starts one period after the calculation date. For example, D19 has 4,811,138 and he has it labeled 11/1/04. That $4.8 million is actually the 10/1/04 value of all of the 195,300 payments. So the 194,006 is the 2/1/07 value of the 3/1/07 payment. To move the calc date ahead one month, he can multiply his NPV function by (1+.08/12). So the 11/1/04 value of all of the 195,300 payments is $4.8 million * (1+.08/12) and the 3/1/07 value of the 3/1/07 payment is 194,006*(1+.08/12) = 195,300.
I also agree that It is very fishy that cells D7 and D8 have hard coded numbers and not the formula. The formula gives considerably larger numbers than what’s typed in there. Is the lawyer trying to understate the value of the payments?
BTW, I hate merged cells.
-
WSmnehring
AskWoody LoungerSeptember 24, 2003 at 4:51 pm #719433Taking both of your comments and working with him, we have figured it out. The first two rows of data should be ignored. Take a look at this improved attached spreadsheet and look at the formula. Personally, I don’t really like doing it this way, but it made him happy and gave him the result he wanted.
-
WSsdckapr
AskWoody LoungerSeptember 24, 2003 at 5:06 pm #719445It looks fine.
What his formula “says” to me is that the “prepayment” is the Royalty received for the month + the “net present value” assuming 8% ann interest of the monthly amounts to be received from next month to March 2007
If the first 2 rows are to be ignored, why are they there?
Steve
-
WSsdckapr
AskWoody LoungerSeptember 24, 2003 at 5:06 pm #719446It looks fine.
What his formula “says” to me is that the “prepayment” is the Royalty received for the month + the “net present value” assuming 8% ann interest of the monthly amounts to be received from next month to March 2007
If the first 2 rows are to be ignored, why are they there?
Steve
-
WSchipshot
AskWoody Lounger -
WSchipshot
AskWoody Lounger
-
-
WSmnehring
AskWoody LoungerSeptember 24, 2003 at 4:51 pm #719434Taking both of your comments and working with him, we have figured it out. The first two rows of data should be ignored. Take a look at this improved attached spreadsheet and look at the formula. Personally, I don’t really like doing it this way, but it made him happy and gave him the result he wanted.
-
Viewing 0 reply threads -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
Outdated Laptop
by
jdamkeene
4 hours, 17 minutes ago -
Updating Keepass2Android
by
CBFPD-Chief115
9 hours, 42 minutes ago -
Another big Microsoft layoff
by
Charlie
9 hours, 22 minutes ago -
PowerShell to detect NPU – Testers Needed
by
RetiredGeek
19 minutes ago -
May 2025 updates are out
by
Susan Bradley
9 hours, 46 minutes ago -
Windows 11 Insider Preview build 26200.5600 released to DEV
by
joep517
15 hours, 26 minutes ago -
Windows 11 Insider Preview build 26120.3964 (24H2) released to BETA
by
joep517
15 hours, 27 minutes ago -
Drivers suggested via Windows Update
by
Tex265
15 hours, 18 minutes ago -
Thunderbird release notes for 128 esr have disappeared
by
EricB
13 hours, 2 minutes ago -
CISA mutes own website, shifts routine cyber alerts to X, RSS, email
by
Nibbled To Death By Ducks
22 hours, 18 minutes ago -
Apple releases 18.5
by
Susan Bradley
16 hours, 43 minutes ago -
Fedora Linux 40 will go end of life for updates and support on 2025-05-13.
by
Alex5723
23 hours, 44 minutes ago -
How a new type of AI is helping police skirt facial recognition bans
by
Alex5723
1 day ago -
Windows 7 ISO /Windows 10 ISO
by
ECWS
7 hours, 37 minutes ago -
No HP software folders
by
fpefpe
1 day, 8 hours ago -
Which antivirus apps and VPNs are the most secure in 2025?
by
B. Livingston
5 hours, 22 minutes ago -
Stay connected anywhere
by
Peter Deegan
1 day, 13 hours ago -
Copilot, under the table
by
Will Fastie
1 day, 4 hours ago -
The Windows experience
by
Will Fastie
1 day, 19 hours ago -
A tale of two operating systems
by
Susan Bradley
1 hour, 15 minutes ago -
Microsoft : Resolving Blue Screen errors in Windows
by
Alex5723
2 days ago -
Where’s the cache today?
by
Up2you2
2 days, 16 hours ago -
Ascension says recent data breach affects over 430,000 patients
by
Nibbled To Death By Ducks
2 days, 9 hours ago -
Nintendo Switch 2 has a remote killing switch
by
Alex5723
1 day, 9 hours ago -
Blocking Search (on task bar) from going to web
by
HenryW
11 hours, 40 minutes ago -
Windows 10: Microsoft 365 Apps will be supported up to Oct. 10 2028
by
Alex5723
3 days, 9 hours ago -
Add or Remove “Ask Copilot” Context Menu in Windows 11 and 10
by
Alex5723
3 days, 9 hours ago -
regarding april update and may update
by
heybengbeng
3 days, 11 hours ago -
MS Passkey
by
pmruzicka
2 days, 13 hours ago -
Can’t make Opera my default browser
by
bmeacham
3 days, 18 hours ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.