PV and Interest Calculation Help

V

VegaOne16

New Member
Joined
Sep 6, 2009
Messages
28
Office Version
  1. 365
Platform
  1. Windows
Hello,

A spreadsheet I recently inherited has hard coded numbers, I was hoping someone could help me with the formulas to recompute a couple columns (PV Cash & Interest Exp) that I can't seem to re-create.

Discount rate is 10%, term is 1/1/2020 - 12/31/2020, it's on a 13 period fiscal year.

Here is a snapshot of the data:

[TABLE="class: outer_border, width: 1000"]
<tbody>[TR]
[TD]Period[/TD]
[TD]Period Days[/TD]
[TD]Cash Outflow[/TD]
[TD]Straight line Expense[/TD]
[TD]PV Cash[/TD]
[TD]Interest Exp[/TD]
[TD]Asset Balance[/TD]
[TD]Liability Balance[/TD]
[TD]Begin Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11,467.60[/TD]
[TD]11,467.60[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl66, width: 73"]1/1/2020[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl66, width: 73"]1/26/2020[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]26[/TD]
[TD]1,000.00[/TD]
[TD]852.46[/TD]
[TD]1,000.00[/TD]
[TD]74.75[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]10,689.89[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]10,542.35[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl66, width: 73"]1/27/2020[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl66, width: 73"]2/23/2020[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]28[/TD]
[TD]1,000.00[/TD]
[TD]918.03[/TD]
[TD]991.55[/TD]
[TD]74.78[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]9,846.64[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]9,617.13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl66, width: 73"]2/24/2020[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl66, width: 73"]3/22/2020[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]28[/TD]
[TD]1,000.00[/TD]
[TD]918.03[/TD]
[TD]983.71[/TD]
[TD]67.94[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]8,996.55[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]8,685.07[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl66, width: 73"]3/23/2020[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl66, width: 73"]4/19/2020[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]28[/TD]
[TD]1,000.00[/TD]
[TD]918.03[/TD]
[TD]975.40[/TD]
[TD]61.59[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]8,140.11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]7,746.66[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl66, width: 73"]4/20/2020[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl66, width: 73"]5/17/2020[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TOTAL[/TD]
[TD]TOTAL
[/TD]
[TD]TOTAL[/TD]
[TD]TOTAL[/TD]
[TD]TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]366[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]11,467.60[/TD]
[TD]532.40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Will forever be in your debt if you can assist with the formulas.

Thank you for your consideration.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You must log in or register to reply here.

Similar threads

H
  • Question Question
Interest calculation
Replies
1
Views
692
James006
James006
O
  • Question Question
Irregular Cash-flows Effecting Project Payout
Replies
0
Views
457
olbucky
O
C
  • Question Question
need help calculation a compound interest spreadsheet for commissions
Replies
0
Views
476
canoli
C
D
  • Question Question
Find interest for the last financial year in an offset column and indirect reference
Replies
0
Views
378
Dares2
D
R
  • Question Question
Calculate total Interest paid
2
Replies
10
Views
2K
xchaser
X

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top