7. True case, slightly stylized: The GMC Denali Ultimate is $91,000. The Dodge Limited, fully equipped truck is $81,000. Yes, trucks are the new luxury vehicles... You have a Ford F150 to trade and the GMC dealer wants it as he owns a Ford dealership too, but the Dodge dealer does not really like mixing brands in the used car section. As a result, they potentially offer you very different trade in values, and have different financing offers as well. Cost of GMC truck Trade Value used as downpayment Amount to finance: Rate per year: payments/year: years: effective r per period: number of periods: payment per month: Cost of Dodge Truck Solve for trade value required to make the payment the same Amount to finance: Rate per year: payments/year: years: number of periods: effective r per period: payment per month: 91,000 30,660 (trade value) 6% 12 6 81,000 4% 12 6 calcualte this first.... (hint - use Goal Seek or Solver to find this value such that the monthly payments are the same - you can use a staring guess of $20,000) 1 1 Syntax USPV(r,t) SPPV(r,t) SFD(r,t) USCA(r,t) Description CR(r,t) Uniform Series Present Value Single Pay Present Value Sinking Fund Deposit Uniform series compound amount SPCA(r,t) Single pay compound amount Capital Recovery (loan payment) r 0.04 0.05 0.03 0.04 0.04 0.005 t 6 10 20 5 5 360 Result Formula 5.24214 = P 0.61391 V V(1+r)" = 0.03722 P-V 5.41632 1.21665 0.006 [(1+r)^-1] 7(1+r)" V = P 7 (1+r)" (1+r)-1 V = Vo(1+r)" 7(1+ 7)" [(1+)-1] P = Vo Excel Financial Formulas ########## =PV(D4, E4,1,0,0) ########## =PV(D5, E5,0,1,0) ########## =PMT(D6,E6,0,1,0) ########## =FV(D7,E7,1,0,0) ########## =FV(D8,E8,0,1,0) #### =PMT(rate,nper,pv,fv,type) (note sign difference on cf) (note sign difference on cf) (note sign difference on cf) (note sign difference on cf) (note sign difference on cf) (note sign difference on cf)